This guide gives you the VLOOKUP syntax as Microsoft documents it, three example formulas you can adapt, and the formula mistakes to fix when a lookup returns an error. It is deliberately short and covers only what Microsoft's own help page confirms.
The four arguments VLOOKUP takes
Microsoft's VLOOKUP help page gives the syntax as:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In order, these are:
- lookup_value: what you are searching for. This can be a cell reference or text in quotes.
- table_array: the range to search.
- col_index_num: the column number within that range whose value you want back.
- range_lookup: in Microsoft's examples, this is
TRUEorFALSE.
Three example formulas you can adapt
These examples come from Microsoft's VLOOKUP page.
Look up a cell value with TRUE
=VLOOKUP(A2,A10:C20,2,TRUE)
This looks up the value in A2 within A10:C20 and returns from column 2 of that range. The last argument is TRUE.
Look up a piece of text with FALSE
=VLOOKUP("Fontana",B2:E7,2,FALSE)
The lookup value is typed text, so it goes in double quotes. The search range is B2:E7, the result comes from column 2 of that range, and the last argument is FALSE.
Look up from another sheet
=VLOOKUP(A2,'Client Details'!A:F,3,FALSE)
The range points at columns A to F on a sheet named Client Details. The sheet name contains a space, so it sits inside single quotes, followed by !. The result comes from column 3.
The typo to avoid when copying Microsoft's examples
Don't paste that one as-is. Write FALSE in full, and put any typed text in double quotes, the way the "Fontana" example does.
Fixing a VLOOKUP that returns #N/A, #REF! or #VALUE!
Go through these fixes in order:
- Spell the last argument in full. A misspelling like the
FLASEin one of Microsoft's own examples won't work, so retype it. - Quote typed text. If you type the lookup value instead of pointing at a cell, wrap it in double quotes:
"Fontana", notFontana. - Quote sheet names that contain spaces. Write
'Client Details'!A:F, with single quotes around the name and!after it. - Keep the column number inside the range.
col_index_numcounts columns withintable_array, not across the whole sheet. In=VLOOKUP(A2,A10:C20,2,TRUE), column 2 means column B, because the range starts at column A. - Check the value is really there. Make sure what you are looking for actually appears in the range you gave, not in a column outside it.
If the formula still fails after those fixes, Microsoft keeps a separate help page for each of these errors, linked from the main VLOOKUP page. For #N/A, its guide on how to correct a #N/A error covers the remaining causes.
When to use XLOOKUP instead
Microsoft's VLOOKUP page describes XLOOKUP as an improved version. According to the page, XLOOKUP works in any direction and returns exact matches by default. If your Excel has it, it is the function Microsoft points you to. Our guide on how to use XLOOKUP in Excel walks through it.
What to check on Microsoft's page before you rely on TRUE
This guide doesn't explain how TRUE and FALSE behave differently. It also doesn't cover which Excel versions support each function, or Google Sheets. Microsoft's VLOOKUP help page has the full argument descriptions and remarks, so read those before you use TRUE on real data. For lookups that VLOOKUP can't handle, Microsoft also has a page on looking up values with VLOOKUP, INDEX or MATCH.
