VLOOKUP
VLOOKUP (Vertical Lookup) is one of Excel's most widely used functions. It searches for a value in the leftmost column of a table and returns a value from a specified column in the same row.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Parameters
| Parameter | Description |
|---|---|
| lookup_value | The value to search for |
| table_array | The table range containing the data |
| col_index_num | The column number to return (1 = first column) |
| range_lookup | FALSE for exact match, TRUE for approximate |
How It Works
- VLOOKUP searches the first column of table_array for lookup_value
- When found, it moves right to the column specified by col_index_num
- It returns that value
Examples
Basic Product Lookup
=VLOOKUP("Widget A", A:C, 3, FALSE)
This searches for "Widget A" in column A and returns the value from column C (the 3rd column).
Employee Salary Lookup
=VLOOKUP(E2, $A$2:$D$100, 4, FALSE)
Looks up employee ID in E2 against the employee table and returns their salary (column 4).
Common Mistakes
- Lookup column not first: VLOOKUP can only search the leftmost column
- Forgetting FALSE: Without FALSE, VLOOKUP may return incorrect approximate matches
- Column index wrong: Count from the first column of table_array, not the sheet
- Not locking ranges: Use $ to make references absolute when copying formulas
VLOOKUP vs XLOOKUP
XLOOKUP (Excel 2021+/365) is the modern replacement:
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search direction | Right only | Any direction |
| Default match | Approximate | Exact |
| Error handling | Needs IFERROR | Built-in |
| Return multiple columns | No | Yes |
When to Use VLOOKUP
- Sharing files with users on older Excel versions
- Simple, right-direction lookups
- Legacy workbooks that already use VLOOKUP
Best Practices
- Always use FALSE for exact matches
- Lock your table range with absolute references ($)
- Consider switching to XLOOKUP for new workbooks
- Use named ranges for cleaner formulas