The LOOKUP function looks for a value in the search range and returns its corresponding value from the result range.
If the result range is not specified, the function returns the searched value itself or the largest value not exceeding the searched value from the search range.
This function is especially useful for searching data in spreadsheets.
Syntax
LOOKUP(lookup_value, search_range, [result_range])
Argument |
Description |
Permitted values |
|---|---|---|
lookup_value |
Value to search for in the row or column of the “search_range” argument |
Any value |
search_range |
Row or column in which to search for the value of the “lookup_value” argument |
Range of cells or array of data |
result_range |
(optional) Range to return the result from |
Range of cells or array of data |
Examples of use
Simple search
=LOOKUP(3, A1:A5, B1:B5)
If the range A1:A5 contains numbers and B1:B5 contains the corresponding values, the function will return the value from B1:B5 that corresponds to the number 3.
Search with an unspecified range for return
=LOOKUP(5, A1:A5)
The function will return the value 5 if it is in the range A1:A5.
Use with text
=LOOKUP(“A”, C1:C5, D1:D5)
If C1:C5 contains letters and D1:D5 contains the corresponding values, the function will return the value from D1:D5 that corresponds to the letter “A”.
Notes
–The search range must be of the same size as the result range.
–The LOOKUP function works with sorted arrays. If the arrays are not sorted, the results may be unpredictable.
–If the lookup_value is not found, the function will return the #N/A error.
–The vector form requires that the vector being looked up be sorted in ascending order.