LOOKUP
Description
Syntax
LOOKUP(search_key, search_range|search_result_array, [result_range])
search_key
- The value to search for in the row or column. For example, 42, "Cats", or I24.search_range|search_result_array
- One method of usingLOOKUP
is to provide a single row or columnsearch_range
to look through for the search with a second argumentresult_range
. The other way is to combine these two arguments into onesearch_result_array
where the first row or column is searched and a value is returned from the last row or column in the array.result_range
- [ OPTIONAL ] - The range from which to return a result. The value returned corresponds to the location wheresearch_key
is found insearch_range
. This range must be only a single row or column and should not be used if using thesearch_result_array
method.
Sample Usage
LOOKUP(10003, A1:A100, B1:B100)
LOOKUP(10003, A1:B100)
LOOKUP("foo", A1:Z10)
How to use LOOKUP
In order to use the LOOKUP formula, start with your edited Excellentable:
Then type in the LOOKUP Formula in the area you would like to display the outcome:
By adding the values you would like to calculate the LOOKUP formula for, Excellentable will generate the outcome:
Notes
The LOOKUP
function will only work properly if data in search_range
or search_result_array
is sorted. Use VLOOKUP
, HLOOKUP
, or other related functions if data is not sorted.
If search_key
is not found, the item used in the lookup will be the value that's immediately smaller in the range provided. For example, if the data set contains the numbers 1, 3, 5 and search_key
is 2, then 1 will be used for the lookup.
In the search_result_array
method, the last row or column in the provided range is returned. If a different row or column is desired, use VLOOKUP
or HLOOKUP
instead.
When using the search_result_array
method, if the range provided contains more columns than rows, then the search will be from left to right over the first row in the range. If the range contains an equal number of rows and columns or more rows than columns, then the search will be from top to bottom over the first column in the range.
See Also
VLOOKUP
: 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.
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.
MATCH
: Returns the relative position of an item in a range that matches a specified value.