VLOOKUP
Lookup and referenceFinds a value in the first column of a table and returns something from a column to its right.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_valueThe value to search for. It is matched against the first column of table_array and nothing else.
- table_arrayThe range to search. Its first column must be the one containing lookup_value. Lock it with absolute references ($A$2:$D$5) if you plan to fill the formula down.
- col_index_numWhich column of table_array to return, counted from 1 at its left edge — not from column A of the sheet.
- range_lookupOptionalFALSE for an exact match, TRUE for an approximate one. It defaults to TRUE, which is almost never what you want — always pass FALSE unless you are deliberately banding values.
Worked example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Stock | Price |
| 2 | Cordless drill | Power tools | 24 | 89.99 |
| 3 | Extension lead | Electrical | 140 | 12.5 |
| 4 | Safety goggles | Safety | 68 | 7.25 |
| 5 | Work gloves | Safety | 210 | 5.4 |
=VLOOKUP("Safety goggles", A2:D5, 4, FALSE)Returns7.25