VLOOKUP
Searches for a value in the first column of a table and returns a value in the same row from a column you specify.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Arguments
lookup_valueThe value to search for in the first columntable_arrayThe table range containing the datacol_index_numThe column number to return the value fromrange_lookupTRUE for approximate match, FALSE for exact match(optional)
=VLOOKUP("Apple",A1:C10,2,FALSE)Finds 'Apple' and returns the value from column 2
=VLOOKUP(A1,Products!A:D,3,FALSE)Looks up A1 in another sheet and returns column 3
=VLOOKUP(100,A1:B10,2,TRUE)Finds the closest value less than or equal to 100
- •Always use FALSE for exact match unless you need approximate matching
- •VLOOKUP can only search the leftmost column of your table
- •Consider using INDEX/MATCH or XLOOKUP for more flexibility
- •Leaving range_lookup blank or set to TRUE - Excel then hands you the closest match instead of the one you actually asked for, with no warning
- •Trying to pull a value from a column to the left of your search column. VLOOKUP simply can't do that, it only looks right
- •Copying the formula down with a relative table_array reference, so row 50 is suddenly searching the wrong table. Lock it with $A$1:$C$100 or a named range
XLOOKUPThe modern option - looks in any direction and skips the column-counting entirelyINDEXPair it with MATCH when you need to search to the left, something VLOOKUP flatly refuses to doMATCHTeams up with INDEX for a lookup that isn't stuck on the leftmost columnHLOOKUPSame idea as VLOOKUP, just turned 90 degrees - it searches the first row insteadWhy does VLOOKUP return the wrong value or #N/A even though the data is right there?
Nine times out of ten, it's range_lookup left blank or set to TRUE, which quietly triggers an approximate match. Set it to FALSE and most of these issues disappear. If that's not it, check for extra spaces, mismatched data types (text vs number), or a table_array that doesn't actually include your lookup column.
Can VLOOKUP look to the left of the lookup column?
No, and this catches a lot of people out. VLOOKUP only searches its first (leftmost) column and returns values to the right of it. For anything else, INDEX/MATCH or XLOOKUP is the answer.
Is VLOOKUP on its way out, replaced by XLOOKUP?
Microsoft clearly wants XLOOKUP to take over - it's more flexible and less fussy to write. But VLOOKUP runs in every Excel version ever made and it's baked into millions of existing workbooks, so it's not going anywhere soon. Worth learning both.
Need to translate a formula using VLOOKUP?
Use our translator to convert your complete formula