The idea behind every lookup is the same: take a value you know, find it in a column, and give me back the matching value from another column. What differs is the syntax and how flexible it is.
1 The setup: two tables
| A | B | C | |
|---|---|---|---|
| 1 | Item code | Item name | Price |
| 2 | P-101 | A4 paper | 18.00 |
| 3 | P-102 | Printer ink | 240.00 |
| 4 | P-103 | Office chair | 650.00 |
| A | B | C | |
|---|---|---|---|
| 1 | Code | Quantity | Price |
| 2 | P-102 | 3 | 240.00 |
| 3 | P-101 | 20 | 18.00 |
| 4 | P-900 | 1 | #N/A |
The idea is identical in VLOOKUP and INDEX+MATCH — what changes is how the “return column” is specified: by a column number in VLOOKUP, and by naming the column outright in XLOOKUP and INDEX.
2 XLOOKUP — today’s first choice
- First argument: the value you are looking for.
- Second: the column you search in.
- Third: the column you return from.
- Fourth (optional): what to show when nothing is found — so you do not need IFERROR.
Why XLOOKUP is better
It looks both left and right, does not break when a column is inserted, needs no column number, and handles “not found” itself. But it is only available in Microsoft 365 and Excel 2021 or later — so if you share the file with someone on an older version, use the alternatives below.
3 VLOOKUP — the most widespread
| Argument | What it means | Warning |
|---|---|---|
| A2 | The lookup value | — |
| $A$2:$C$4 | The table range | Pin it with dollars before filling down |
| 3 | The number of the column returned | Its meaning changes if a new column is inserted |
| FALSE | Exact match | Forgetting it gives wrong results rather than errors |
Three limitations you must know
First: it searches only the first column of the range and cannot look leftwards. Second: the column number is fixed, so inserting a column in the middle silently breaks every formula. Third: omitting FALSE makes it accept the “closest value”, returning another item’s price without you noticing — an error that slips into reports very easily.
4 INDEX + MATCH — the flexible alternative
MATCH
Finds the row number holding the code
0
Exact match
INDEX
Returns the value from that row
The result
The item’s price
MATCH decides “where”, INDEX fetches “what” — and it works in every version
| Criterion | XLOOKUP | VLOOKUP | INDEX+MATCH |
|---|---|---|---|
| Looks leftwards | Yes | No | Yes |
| Survives inserted columns | Yes | No | Yes |
| Handles “not found” | Built in | With IFERROR | With IFERROR |
| Works in older versions | No | Yes | Yes |
| Readability | High | Medium | Takes getting used to |
5 Why does #N/A appear when the value is there?
- A hidden space on one side or the other — fix it with TRIM.
- A number against text: code 1001 is a number in one table and text in the other.
- Look-alike characters: the letter O against the digit 0, or a stray non-Latin character inside a code.
- An unpinned range that slid as you filled down.
- The code genuinely is not there — a correct result that deserves following up, not hiding.
Lesson summary
- XLOOKUP is the first choice if your version supports it.
- VLOOKUP always needs FALSE and a pinned range.
- INDEX+MATCH is an alternative that works in every version and survives column changes.
- #N/A is usually a space or a type mismatch, not a fault in the function.
- Handle “not found” with an intelligible message, not with blind suppression.
6 Test your understanding
Three quick questions
Pick the answer you believe is correct and you will see the result immediately.
1. The lookup column sits to the right of the column you need to return, and you are on Excel 2016. The solution:
VLOOKUP cannot look leftwards, and XLOOKUP is not available in 2016 — so INDEX+MATCH is the answer.
2. What is the danger of omitting FALSE in VLOOKUP?
The most dangerous errors are the ones that never show — a wrong figure in a report that looks perfectly fine.
3. #N/A for a code you can see with your own eyes in both tables. The first thing to check:
Try =A2=Items!A5 — if it returns FALSE, the two values really are different.