VLOOKUP is one of Excel's classic lookup functions. It searches for a value in the first column of a selected table and returns a value from another column in the same row.
VLOOKUP Syntax
- lookup_value: The value you want to find.
- table_array: The table containing the lookup column and return columns.
- col_index_num: The number of the return column within the selected table.
- range_lookup: TRUE for approximate matching or FALSE for exact matching.
Basic Exact-Match Example
Suppose product IDs are in column A and product names are in column B. If the ID to find is in E2:
The final argument FALSE tells Excel to look for an exact match.
Why FALSE Matters
For IDs, codes and names, exact matching is normally what you want. Omitting the final argument can cause Excel to use approximate matching, which may return an unexpected result when the lookup table is not arranged correctly.
Understanding the Column Index
The column index starts at 1 from the first column of the selected table. If your table is A2:D100, then A is column 1, B is 2, C is 3 and D is 4.
This is a common source of errors because the index is relative to the selected table, not the worksheet's absolute column number.
Approximate Match
Approximate matching can be useful for ranges such as tax bands, grades or pricing thresholds. When using it, the first column of the lookup table generally needs to be sorted in ascending order for predictable results.
Use IFERROR When a Missing Match Is Expected
This can make reports easier to read. However, do not use error handling to hide data-quality problems you actually need to investigate.
Common VLOOKUP Problems
Lookup column is not the first column
VLOOKUP expects the search column to be the first column of the selected table. If you need to return a value from a column to the left of the lookup column, VLOOKUP is not the natural choice.
Numbers stored as text
A numeric ID and a text ID can look the same but behave differently. Check the source data.
Extra spaces
Hidden spaces in text can prevent an exact match.
Wrong column index
Recount the columns from the first column of the selected table whenever you change the table range.
VLOOKUP vs XLOOKUP
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Exact-match default | No | Yes |
| Left lookup | Not directly | Yes |
| Column index | Required | No |
| Not-found option | Usually IFERROR | Built in |
If your Excel version supports XLOOKUP, it is often easier to maintain for new lookup formulas. VLOOKUP remains important because it is widely used in existing workbooks.
How to Check a VLOOKUP Result
Test the formula with a value you can verify manually. Check that the lookup key is unique, the expected row is being returned and the return column is correct.
VLOOKUP Checklist
- Use FALSE for ordinary exact-match lookups.
- Count the return column from the first table column.
- Check for duplicates and hidden spaces.
- Check number/text consistency.
- Do not hide errors that indicate real data problems.
For more Excel workflows, see Taskvora's XLOOKUP guide and explore the Excel Tools collection.
More Excel help: See IF in Excel, SUMIF and SUMIFS, COUNTIF and COUNTIFS, INDEX and MATCH, Remove Duplicates and PivotTables.
Related Excel guide: How to Clean Data in Excel.
Related Excel guide: How to Sort and Filter Data in Excel.
Related Excel guide: How to Compare Two Columns in Excel.