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

Lookup employee department

Grade lookup with approximate match

Three arguments (defaults to approximate match)

Custom value when not found

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.
• 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

---

← Back to Formula Reference