VLOOKUP
VLOOKUP only works on one shape of table: the key column on the far left of the range, everything you might want back to the right of it. That single constraint settles most of the questions on this hub, several of which are really asking for INDEX MATCH or XLOOKUP — both take the search range and the answer range as separate arguments, so neither has the rule. The rest are one of two failures. A #N/A on a value you can plainly see is a stray space or a number stored as text far more often than a broken formula, and a wrong-but-plausible figure with no error at all means the exact-match argument was left off.
The formula
=VLOOKUP(F2, $A$2:$C$500, 3, FALSE) exact match; 3 means the third column of A:C, not column C of the sheet
=VLOOKUP(F2, Sheet2!$A$2:$D$500, 4, FALSE) the lookup table on another sheet
=VLOOKUP(F2, '[Prices.xlsx]Rates'!$A$2:$C$400, 3, FALSE) another workbook; keep the full path or it breaks when the file moves
=IFERROR(VLOOKUP(F2, $A$2:$C$500, 3, FALSE), "") blank instead of #N/A when there is genuinely no match
=VLOOKUP(F2&"|"&G2, $A$2:$D$500, 4, FALSE) two criteria — column A must already hold the same joined key
=VLOOKUP(TRIM(F2), $A$2:$C$500, 3, FALSE) the usual cure when the value is visibly there but #N/A comes backA worked example
A2:C500 is an unsorted price list — SKU in column A, description in B, unit price in C. F2 holds the SKU being looked up, SKU-1042.
=VLOOKUP(F2, $A$2:$C$500, 3, FALSE)
24.90, the price on the row whose column A equals SKU-1042. Change the 3 to a 2 and the description comes back instead. Now insert a new column between B and C: the range stretches to $A$2:$D$500 by itself, but the 3 does not move, so the formula quietly starts returning the new empty column with no error to warn anyone.
Which one do I need?
| If you want to… | Use |
|---|---|
| A straight lookup where the key column sits left of what you want back | =VLOOKUP(key, range, n, FALSE) — never leave the FALSE off |
| What you want back is to the LEFT of the key column | VLOOKUP structurally cannot do it; INDEX/MATCH names the two ranges separately |
| Everyone opening the file has Excel 365 or 2021 | XLOOKUP does the same job in either direction and defaults to an exact match |
| The key is two columns together, such as branch plus product | Build one joined key column on both sides and look that up, or switch to XLOOKUP with joined arrays |
| The lookup table lives in another workbook | Reference it with the full path; the last calculated values persist while that file is closed, and become #REF! if it is moved or renamed |
| Several rows match and you need all of them | VLOOKUP returns only the first match, by design |
| The headers run across a row instead of down a column | HLOOKUP is the sideways twin, with the same approximate-match trap |
| #N/A on a value you can see in the table | Test it with =F2=A5 first; a FALSE there means a trailing space or a number stored as text, not a broken formula |
Frequently asked questions
What does the FALSE at the end of VLOOKUP actually do?
It forces an exact match. Omitting it, or passing TRUE, asks for an approximate match, which assumes the first column is sorted ascending and otherwise returns the nearest lower value it happens to land on — with no error at all. That silent wrong answer is why FALSE belongs in essentially every VLOOKUP you write.
Why does VLOOKUP return #N/A when the value is clearly in the table?
The two values are not identical even though they look it: a trailing space, a non-breaking space pasted from a web page, or "1042" stored as text against 1042 stored as a number. The other common cause is structural — the value being searched for has to sit in the FIRST column of table_array, not just somewhere inside it.
Can VLOOKUP look to the left?
No. It always searches the first column of the range and returns something to the right of it. To pull a value from a column left of the key, use INDEX with MATCH, or XLOOKUP on Excel 365 and 2021, both of which take the search range and the return range as separate arguments.
VLOOKUP, INDEX MATCH or XLOOKUP — which should I use?
XLOOKUP when everyone who opens the file has Excel 365 or 2021, since it is the shortest and defaults to an exact match. INDEX MATCH when the workbook has to survive older versions or a layout where columns get inserted. VLOOKUP is still fine for a small, stable table where the key is genuinely the leftmost column.
New guides and tools, once a month
DE + EN · double opt-in · no spam