How to use VLOOKUP in Excel
VLOOKUP searches for a value in the first column of a table and returns a value from another column on the same row. It is useful when you have a list of IDs or product names and want to find details such as a price or department.
VLOOKUP formula
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: the item you want to find.
- table_array: the cells that contain the lookup list and result.
- col_index_num: the number of the result column, counting from the left edge of the table.
- range_lookup: use FALSE for an exact match.
Example: find a product price
Enter this table in cells A1:B4. The product names must be in the first column of the selected table.
| Row | A: Product | B: Price |
|---|---|---|
| 1 | Product | Price |
| 2 | Keyboard | 25 |
| 3 | Mouse | 12 |
| 4 | Monitor | 180 |
Type Mouse in D2. In E2, enter:
=VLOOKUP(D2, A2:B4, 2, FALSE)The result is 12. Excel looks for Mouse in column A and returns the value from the second column of A2:B4.
Common VLOOKUP problems
- Use FALSE for an exact match. TRUE or an omitted final argument allows approximate matching and usually needs the first column sorted.
- The lookup value must be in the leftmost column of the selected table. VLOOKUP cannot return a value from a column to the left.
- The column number starts at 1 from the left edge of the selected table, not from the worksheet column letter.
- If there are duplicate lookup values, VLOOKUP returns the first match it finds.
- Extra spaces or numbers stored as text can prevent values from matching.
Newer Excel versions include XLOOKUP, which can search in either direction. For a lookup using INDEX and MATCH, see the INDEX tutorial. To join two spreadsheets by a shared column, try the Excel Lookup tool.