How to use VLOOKUP in Excel (with XLOOKUP examples)
VLOOKUP finds a value in the first column of a range and returns a value from another column in the same row:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Example: =VLOOKUP(E2, A2:C100, 3, FALSE) looks up E2 in column A and returns the matching value from column C. Always use FALSE for an exact match. In Excel 365 and 2021+, XLOOKUP is simpler: =XLOOKUP(E2, A2:A100, C2:C100).
Applies to all versions of Excel. XLOOKUP is available in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web.
Write a VLOOKUP formula
Say you have product IDs in column A, names in column B and prices in column C, and you want the price for the ID typed in E2.
- Click the cell where you want the result, for example
F2. - Type
=VLOOKUP(and click the cell with the value to find (E2), then type a comma. - Select the table, starting with the column that contains the IDs:
A2:C100. Press F4 to make it absolute ($A$2:$C$100) so it doesn't shift when you copy the formula. Type a comma. - Type the column number to return, counting from the first column of your table: price is the 3rd column. Type a comma.
- Type
FALSEfor an exact match, close the bracket and press Enter:=VLOOKUP(E2,$A$2:$C$100,3,FALSE). - Drag the fill handle down to copy the formula to other rows.
The four arguments explained
| Argument | What it means |
|---|---|
| lookup_value | The value you're searching for (a cell or text in quotes) |
| table_array | The range to search; the lookup column must be its first column |
| col_index_num | Which column of that range to return (1 = first column) |
| range_lookup | FALSE = exact match; TRUE or omitted = approximate match (needs sorted data) |
Fixing #N/A and other VLOOKUP errors
- #N/A - no match found. Check for extra spaces (
=TRIM(A2)), numbers stored as text (green triangle in the corner), or a different spelling. Wrap the formula to show a friendly message:=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). - Wrong result - you probably left out
FALSE, so Excel did an approximate match. - #REF! - the column number is bigger than the number of columns in your range.
- Can't look to the left - VLOOKUP only returns columns to the right of the lookup column. Use XLOOKUP or INDEX/MATCH.
XLOOKUP: the modern replacement
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")does the same job as the VLOOKUP above.- Exact match is the default, it can return columns to the left, and inserting a column doesn't break it.
- It isn't available in Excel 2019 or earlier - use
=INDEX(C2:C100, MATCH(E2, A2:A100, 0))there.
Frequently asked questions
Is VLOOKUP case-sensitive?
No. 'abc' and 'ABC' match. Use EXACT with INDEX/MATCH or XLOOKUP if you need case-sensitive lookups.
Can VLOOKUP look up from another sheet?
Yes: =VLOOKUP(E2, Sheet2!$A$2:$C$100, 3, FALSE). For another workbook, open both and select the range; Excel adds the file name.
Why does VLOOKUP return 0?
The matching cell in the return column is empty. Use =IF(VLOOKUP(...)="","",VLOOKUP(...)) or XLOOKUP, which can return blanks.
What does HLOOKUP do?
The same as VLOOKUP but searches across the first row instead of down the first column.