VLOOKUP function
VLOOKUP searches the first column of a table and returns the value from a specified column in the matched row.
Lookup and reference
Where can you use it?
Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.
Scenario in the workbook
Look up a region by employee ID. FALSE requires an exact match.
A worked example from the workbook
| Row | A | B | C |
|---|---|---|---|
| 6 | Value | Comparison | Region |
| 7 | E01 | E01 | North |
| 8 | E02 | E02 | East |
| 9 | E03 | E03 | West |
=VLOOKUP(A7,$B$7:$C$9,2,FALSE)Expected resultNorth
See the inputs and instructions for all 3 examplesCopyable formulas
=VLOOKUP(E2,A2:C4,3,0)=DÜŞEYARA(E2;A2:C4;3;0)Sample data
Enter the headers and sample records in A1:C4.
| ID | Product | Price |
|---|---|---|
| K-10 | Cable | 125 |
| K-20 | Socket | 150 |
| K-30 | Switch | 95 |
- E2 = K-20
Enter the formula in E3.
To try the Turkish formula, use the sample data on the Turkish page.
Expected result
150
How it works
The 3 selects the third column of A2:C4. The final 0 requests an exact match. A missing ID such as K-99 produces a lookup error. Check the column index again if you insert a column into the table.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Compatibility
Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024.
Related pages
Worked examples
Look up a region by employee ID. FALSE requires an exact match.
Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.
| Row | A | B | C |
|---|---|---|---|
| 6 | Value | Comparison | Region |
| 7 | E01 | E01 | North |
| 8 | E02 | E02 | East |
| 9 | E03 | E03 | West |
| Case | Formula | Expected result |
|---|---|---|
| Record 1 | =VLOOKUP(A7,$B$7:$C$9,2,FALSE) |
North |
| Record 2 | =VLOOKUP(A8,$B$7:$C$9,2,FALSE) |
East |
| Record 3 | =VLOOKUP(A9,$B$7:$C$9,2,FALSE) |
West |