VLOOKUP
Returns a value from a table by looking up a value in the first column and returning a value from the specified column
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup], [value_if_not_found])
Arguments
| Argument | Description | Required |
|---|---|---|
lookup_value |
The value to search for in the first column of the table | Required |
table_array |
The table to search in | Required |
col_index_num |
The column number in the table to return a value from | Required |
range_lookup |
TRUE for approximate match, FALSE for exact match. Defaults to TRUE. | Optional |
value_if_not_found |
Value to return if lookup value is not found. Defaults to error. | Optional |
Examples
Find product price by ID
=VLOOKUP(2, A1:C3, 3, false)
Result ⇒19.99
| A | B | C | |
| 1 | 1 | Widget | 9.99 |
| 2 | 2 | Gadget | 19.99 |
| 3 | 3 | Tool | 29.99 |
Lookup employee department
=VLOOKUP("Jane", A1:B3, 2, false)
Result ⇒Marketing
| A | B | |
| 1 | John | Sales |
| 2 | Jane | Marketing |
| 3 | Bob | IT |
Grade lookup with approximate match
=VLOOKUP(85, A1:B4, 2, true)
Result ⇒B
| A | B | |
| 1 | 60 | D |
| 2 | 70 | C |
| 3 | 80 | B |
| 4 | 90 | A |
Three arguments (defaults to approximate match)
=VLOOKUP(25, A1:B3, 2)
Result ⇒Medium
| A | B | |
| 1 | 10 | Small |
| 2 | 20 | Medium |
| 3 | 30 | Large |
Custom value when not found
=VLOOKUP("Z", A1:B3, 2, false, "Not Found")
Result ⇒Not Found
| A | B | |
| 1 | A | Alpha |
| 2 | B | Beta |
| 3 | C | Charlie |
Related Functions
Other Data functions:
- CHOOSE - Uses index to return a value from the list of value arguments
- COLUMN - Returns the column number of the current cell context.
- DROP - Drops the first N rows and optionally first N columns from an array. Returns an error if the result would be empty.
- FILTER - Returns a filtered array containing only rows that match the criteria
- GROUPBY - Groups data by a key and applies an aggregator function to each group
- HLOOKUP - Returns the corresponding value from the specified row of table which matches exactly or approximately to the first row of the table.
- HSTACK - Horizontally stacks arrays by appending columns
- INDEX - Returns a cell value from a list or table based on its column and row numbers.
- LAST - Returns the last N rows and optionally last N columns from an array. Returns an error if the result would be empty.
- MAP - Processes each row in an array with a formula including a COL or CELL keyword and returns it as a 2D array.
- MATCH - Returns the position of the item/lookup_value in the lookup_array or range.
- PICK - Selects specific columns from an array using column numbers or header names
- ROW - Returns the 1-indexed row number of the current cell
- SEQUENCE - Returns the list of generated sequential numbers in an array.
- SORT - Sorts the values in a range or array.
- SORTBY - Sorts a range or array based on values in one or more corresponding ranges or arrays.
- TAKE - Returns the first N rows and optionally first N columns from an array. Returns an error if the result would be empty.
- TRANSPOSE - Returns the result after transposing the rows and columns of an array or range.
- UNIQUE - Returns a list of unique values in a list or range.
- VSTACK - Vertically stacks arrays by appending rows
- XLOOKUP - Searches a range or array for a match and returns the corresponding item from a second range or array. Supports exact and approximate matching, wildcards, and reverse search.