Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
Sample Usage
=VLOOKUP(10003, A2:B26, 2, FALSE)
Syntax
=VLOOKUP(search_key, range, index, [is_sorted])
-
search_key
- The value to search for. For example,42
,"Cats"
, orI24
. -
range
- The range to consider for the search. The first column in the range is searched for the key specified insearch_key
. -
index
- The column index of the value to be returned, where the first column inrange
is numbered 1.-
If
index
is not between 1 and the number of columns inrange
, #VALUE! is returned.
-
-
is_sorted
- [TRUE
by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted. FALSE is recommended in most cases.-
It’s recommended to set
is_sorted
toFALSE
. If set toFALSE
, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found. -
If
is_sorted
isTRUE
or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.
-
Notes
-
If
is_sorted
is set toTRUE
or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned. IfVLOOKUP
doesn’t appear to be giving correct results, check that the last argument is set toFALSE
. If the data is sorted and you need to optimize for performance, set it toTRUE
. In most cases it should be set toFALSE
. -
When searching for numeric or date values, make sure that the first column in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
-
Search keys based on regular expressions are NOT supported. Use
QUERY
instead. -
VLOOKUP
has much better performance with sorted ranges andis_sorted
set toTRUE
. Consider sorting the column being searched. -
You can also find matches using pattern strings that include wildcards. The question mark (?) and asterisk (*) are the wildcards for
search_key
, with the question mark standing in for a single character and the asterisk standing in for any series of characters. If you need to match an actual question mark or asterisk, add a tilde (~) before the character and add an extra tilde if you're looking for something with an actual tilde in it.
See Also
QUERY
: Runs a Google Visualization API Query Language query across data.
HLOOKUP
: Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
Examples
In this example, VLOOKUP
searches down the first column for a student ID and returns the corresponding grade
In this example, VLOOKUP
searches down the first column for the income using approximate match (is_sorted
is set to TRUE
) and returns the corresponding tax rate
VLOOKUP
returns the first value found when there are multiple matches for the search_key