VLOOKUP is the most tested formula in Excel job interviews. Here is the complete guide — syntax, examples, mistakes, and when to use XLOOKUP instead.
VLOOKUP looks down the first column of a table, finds a matching value, and returns something from the same row in another column. The V stands for "vertical" — it searches vertically down a column.
Think of it like a phonebook. You look up a name (the search value), and VLOOKUP returns the corresponding phone number (the return value) from a different column on the same row.
VLOOKUP is one of the most searched Excel formulas on the internet — and one of the most commonly tested in job interviews and Excel assessments.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Description |
|---|---|
| lookup_value required | The value to search for. Can be a value, text, or cell reference. |
| table_array required | The range that contains your data. The lookup value must be in the first column. |
| col_index_num required | The column number to return the result from. 1 = first column, 2 = second column, etc. |
| range_lookup optional | FALSE or 0 for exact match (almost always what you want). TRUE for approximate match. |
Product names are in column A, prices in column B. Look up the price for "Coffee".
=VLOOKUP("Coffee", A2:B10, 2, 0)The 2 means return the value from the 2nd column. The 0 means exact match.
Instead of typing the search value directly, reference a cell. Change E2 and the result updates automatically.
=VLOOKUP(E2, A2:C100, 3, 0)When copying VLOOKUP down a column, lock the table range so it doesn't shift.
=VLOOKUP(E2, $A$2:$C$100, 3, 0)The $ signs lock the range. Without them, the table range shifts as you copy the formula down.
When VLOOKUP can't find a value it returns #N/A. Wrap in IFERROR for a cleaner result.
=IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, 0), "Not found")=VLOOKUP(A2, PriceList!$A:$C, 2, 0)Reference another sheet by typing its name followed by an exclamation mark before the range.
Use multiple VLOOKUP formulas, each with a different col_index_num.
=VLOOKUP(E2, $A$2:$D$100, 2, 0) ← column 2
=VLOOKUP(E2, $A$2:$D$100, 3, 0) ← column 3
VLOOKUP only searches the first column of your table_array. If the value you're searching is in column C but your table starts at column A, the formula returns wrong results. Use INDEX MATCH or XLOOKUP if you need to search a column that isn't first.
If you omit the last argument, VLOOKUP defaults to TRUE (approximate match). This works only when data is sorted ascending and often returns wrong results on unsorted data. Always explicitly type 0 or FALSE for exact match.
If someone inserts a column into your table, the col_index_num is now wrong. Use XLOOKUP or INDEX MATCH if column stability matters.
If you're on Excel 2019 or Microsoft 365, use XLOOKUP. It replaces VLOOKUP with a cleaner syntax, can look in any direction, handles missing values natively, and doesn't break when columns are inserted.
Only use VLOOKUP if you're sharing files with people on older Excel versions, or if you need to maintain compatibility with Google Sheets.
ExcelPro has VLOOKUP exercises in every specialist track — real data, instant feedback, free to start.
Try VLOOKUP exercises →