VLOOKUP

If you have known information on your spreadsheet, you can use VLOOKUP to search for related information by row. For example, if you want to buy an orange, you can use VLOOKUP to search for the price.

VLOOKUP for BigQuery

Syntax

=VLOOKUP(search_key, range, index, [is_sorted])

Inputs

search_key: The value to search for in the first column of the range.
range: The upper and lower values to consider for the search.
index: The index of the column with the return value of the range. The index must be a positive integer.
is_sorted: Optional input. Choose an option:
- FALSE = Exact match. This is recommended.
- TRUE = Approximate match. This is the default if is_sorted is unspecified.
  Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you are likely to get a wrong return value. Learn why you may encounter a wrong return value.

Return value

The first matched value from the selected range.

Technical details:

Example:

=VLOOKUP(G9, B4:D8, 3, FALSE)

=VLOOKUP("Apple", B4:D8, 3, TRUE)

Inputs	Description
`search_key`	This is the value that you search in the first column of the `range`. If you expect a non-error value, the search key must be in the first column of the `range`. Cell reference is also supported. To do a simple check: If your `search_key` is located at B3, then your `range` should start with column B.
`range`	This is the `range` where: The function searches for the specified search key in its first column. `VLOOKUP` returns the value from the column specified by `index`. You can also use a named range. To return a non-error value, your search key must be in the first column of the `range`. To do a simple check: If your `search_key` is located at B3, then your `range` should start with column B.
`index`	Also called 'Column number'. This is the index of the column in the `range` that contains the return value. The smallest possible index is 1. The largest possible index is the maximum number of columns in that `range`. After you set up the range, `VLOOKUP` only looks in the search key column, when index = 1, or columns that are further to the right. Tip: When you use `VLOOKUP`, imagine that the columns of the `range` are numbered from left to right and start with 1.
`is_sorted`	This is an optional input. The two available choices are `TRUE` and `FALSE`. If `is_sorted` is `TRUE`, then `VLOOKUP` uses an approximate match. Important: Before you use an approximate match, sort your search key in ascending order. Otherwise, you may get an unexpected value returned. Learn why you may encounter a wrong return value. If `is_sorted` is `FALSE`, then `VLOOKUP` uses an exact match. If `is_sorted` is not specified, it is `TRUE` by default. We strongly recommend that you: Use `FALSE` for `is_sorted` due to its consistent behaviour whether or not the search key column is sorted. Always specify `is_sorted` for better readability, even though the input is optional.

Outputs Description

Return value

This is the value that VLOOKUP returns based on your inputs. There's only one return value from each VLOOKUP function.

If there are multiple search key values that match, the value in the return value column whose associated search key is first matched in the search key column is returned.
If #N/A is returned, a value isn't found.

If you encounter an unexpected value or error like #N/A or #VALUE!, begin to troubleshoot. If you want to replace #N/A with another value, learn more about how to use IFNA() on VLOOKUP().

Basic VLOOKUP examples:

VLOOKUP on different search keys

Use VLOOKUP to find the price of an Orange and Apple.

Try it out

Explanation:

VLOOKUP on different column indexes

Use VLOOKUP to find the quantity of Oranges in the second index column.

Try it out

Explanation:

VLOOKUP exact match or approximate match

Use VLOOKUP exact match to find an exact ID.
Use VLOOKUP approximate match to find the approximate ID.

Try it out

Explanation:

Common VLOOKUP applications

Replace error value from VLOOKUP

You may want to replace an error value returned by VLOOKUP when your search key doesn't exist. In this case, if you don't want #N/A, you can use IFNA() functions to replace #N/A. Learn more about IFNA().

Try it out

Originally, VLOOKUP returns #N/A because the search key 'Pencil' does not exist in the 'Fruit' column.

IFNA() replaces #N/A error with the second input specified in the function. In our case, it's 'NOT FOUND'.

=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND")

Return value = 'NOT FOUND'

Tip: If you want to replace other errors such as #REF!, learn more about IFERROR().

VLOOKUP with multiple criteria

VLOOKUP can't be directly applied on multiple criteria. Instead, create a new helper column to directly apply VLOOKUP on multiple criteria to combine multiple existing columns.

Try it out

1. You can create a Helper column if you use '&' to combine first name and surname.	=C4&D4 and dragging it down from B4 to B8 gives you the Helper column.
2. Use cell reference B7, JohnLee, as the search key.	=VLOOKUP(B7, B4:E8, 4, FALSE) Return value = 'Support'

VLOOKUP with wildcard or partial matches

In VLOOKUP, you can also use wildcards or partial matches. You can use these wildcard characters:

A question mark '?' matches any single character.
An asterisk '*' matches any sequence of characters.

To use wildcards in VLOOKUP, you must use an exact match: is_sorted = FALSE.

Try it out

'St*' is used to match anything that starts with 'St' regardless of the number of characters, such as 'Steve', 'St1', 'Stock' or 'Steeeeeeve'.

=VLOOKUP("St*", B4:D8, 3, FALSE)

Return value = 'Marketing'

Troubleshoot errors and best practices:

Choose a section to give feedback on

`search_key` is 'Orange'	=VLOOKUP("Orange", B4:D8, 3, FALSE) Return value = $1.01
`search_key` is 'Apple'	=VLOOKUP("Apple", B4:D8, 3, FALSE) Return value = $1.50
`search_key` that uses cell reference of 'Apple' in G9	=VLOOKUP(G9, B4:D8, 3, FALSE) Return value = $1.50

Exact match	=VLOOKUP(G6, A4:D8, 2, FALSE) Return value = 'Apple'
Approximate match	=VLOOKUP(G3, A4:D8, 2, TRUE) OR =VLOOKUP(G3, A4:D8, 2) Return value = 'Banana'

To do	Reason
Use absolute references for `range`	You should use: Absolute reference for `VLOOKUP` `range` VLOOKUP(G3, $B$3:$D$7, 3, FALSE) You should not use: VLOOKUP(G3, B3:D7, 3, FALSE) This prevents unpredictable changes in the `range` when it's copied or dragged down.
Sort the first column in ascending order when you use an approximate match, such as `is_sorted` = `TRUE`.	If you use an approximate match or `is_sorted` = `TRUE`, you must sort the first column in ascending order. Otherwise, you are likely to get a wrong return value. Learn more on how to sort.
Clean your data before you use `VLOOKUP`	Before you use `VLOOKUP`, remember to clean your data. Unclean data may cause `VLOOKUP` to return an unpredictable value. Here are some common pitfalls of unclean data: Spaces that lead: ' apple' Spaces that trail: 'apple ' Blanks or spaces: '' and ' ' aren't equivalent To trim white space that leads and trails, you can use Data Data cleanup Trim whitespace.
Don't store number or date values as text	Make sure that your date or number values in the first column of your `VLOOKUP` range, such as the search key column, aren't stored as text values. You may get an unexpected return value. On the top of Sheets, select your search key column. Tap Format menu Number. Choose an option depending on your desired data type: Date Number

VLOOKUP

Sample usage

Syntax

Syntax

Inputs

Return value

Basic VLOOKUP examples:

VLOOKUP on different search keys

VLOOKUP on different column indexes

VLOOKUP exact match or approximate match

Common VLOOKUP applications

Replace error value from VLOOKUP

VLOOKUP with multiple criteria

VLOOKUP with wildcard or partial matches

Troubleshoot errors and best practices:

Need more help?

Try these next steps:

What's the issue with this selection?

Share additional info or suggestions