This function performs a vertical lookup by searching for a key value (unique identifier) down the first column in a specified range and returns a value in the same row from another column.
VLOOKUP(search_key, range, index, [is_sorted])
search_key- The value to search for. For example,
range- The range to consider for the search. The first column in the range is searched for the key specified in
index- The column index of the value to be returned, where the first column in
rangeis numbered 1.
indexis not between 1 and the number of columns in
is_sorted- [OPTIONAL, TRUE or FALSE,
TRUEby default] - Indicates whether the column to be searched (the first column of the specified range) is sorted.
TRUEor 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,
is_sortedis set to
TRUEor omitted, and the first column of the range is not in sorted order, an incorrect value might be returned.
FALSE, only 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/Ais returned if no such value is found.
VLOOKUP(10003, A2:B26, 2, FALSE)
How to use VLOOKUP
To use the VLOOKUP Formula, start with the Excellentable you would like to edit.
Type the VLOOKUP formula into the cell you have chosen to display the result of the lookup operation:
Enter values for the four required parameters: search_key, range, index, [is_sorted]
When you hit Enter Excellentable will retrieve the lookup value:
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. If regular expression support is required please use the
QUERY function instead.
VLOOKUP has much better performance with sorted ranges and
is_sorted set to
TRUE. 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.
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.