Lookup formula
VLOOKUP in Excel and Google Sheets
Look up a value in the first column of a table and return a value from a column to the right.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value
- The value to find in the first column.
- table_array
- The whole table, starting with the lookup column.
- col_index_num
- Which column of the table to return (1 = first).
- range_lookup
- FALSE for an exact match. Almost always use FALSE.
Example
| Row | A | B | C |
|---|---|---|---|
| 1 | ID | Name | Dept |
| 2 | 101 | Anna | Sales |
| 3 | 102 | Ben | Finance |
| 4 | 103 | Cleo | IT |
| 5 | |||
| 6 | Find ID | 102 |
C6 =VLOOKUP(B6,A2:C4,3,FALSE)
Result: Finance
Finds ID 102 in column A and returns the third column, Dept.
Ready-to-copy formulas
Exact match lookup
=VLOOKUP(A2,Sheet2!A:C,2,FALSE)Hide #N/A when not found
=IFERROR(VLOOKUP(A2,Sheet2!A:C,2,FALSE),"")Lock the table when copying
=VLOOKUP(A2,$E$2:$G$100,3,FALSE)
Tips
- Always set the last argument to FALSE unless the first column is sorted and you want an approximate match.
- VLOOKUP cannot look to the left. Use XLOOKUP or INDEX/MATCH for that.
- Inserting a column inside the table can break col_index_num.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.