Lookup and reference
VLOOKUP function playground
Finds a value in the first column of a table and returns a value from the same row.
The playground runs in your browser. Nothing you type is sent anywhere.
How VLOOKUP works
VLOOKUP goes down the first column of the table until it finds the lookup value, then moves across that row to the column number you gave.
Always add FALSE unless you mean an approximate match. Without it VLOOKUP assumes the first column is sorted and may return the wrong row. VLOOKUP can't look to the left; use XLOOKUP or INDEX and MATCH for that.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | What it means |
|---|---|
lookup_valuerequired | The value to find, such as an employee ID. |
table_arrayrequired | The table to search. The value must be in its first column. |
col_index_numrequired | Which column of the table to return, counting from 1 at the left. |
range_lookupoptional | FALSE for an exact match. TRUE or leaving it out means an approximate match on a sorted list. |
Examples
| Formula | What it does | Result |
|---|---|---|
=VLOOKUP(I1,A2:E9,2,FALSE) | Name for the ID in I1. | Jonas Berg |
=VLOOKUP(I1,A2:E9,4,FALSE) | Salary: the 4th column of the table. | $61,000.00 |
=VLOOKUP("E-107",A2:E9,3,FALSE) | Department of E-107. | Finance |
=VLOOKUP(I1,A2:E9,6,FALSE) | A column number past the table gives #REF!. | #REF! |
Common mistakes
Forgetting FALSEWithout FALSE, an unsorted table can return a wrong match without any error.
Column number is counted inside the table2 means the second column of table_array, not column B of the sheet.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.