1. Spreadsheet.com Support
  2. Functions
  3. Lookup functions

LOOKUP

Looks up a key value in a sorted row or column and returns the corresponding matching record from a different row or column.

Sample Usage

LOOKUP(1234, A1:A10, B1:B10)

LOOKUP(1234, A1:B10)

LOOKUP(“task”, A1:B10)

Syntax

LOOKUP(search_key, search_range|search_result_array, [result_range])

There are two ways to use the LOOKUP function:

LOOKUP(search_key, search_range, result_range)

  • search_key – The value to search for in the search range.

  • search_range – A single row or column in which the search_key will be looked up.

  • result_range – A single row or column in which the corresponding record from the search_key and search_range will be returned.

LOOKUP(search_key, search_result_array)

  • search_key – The value to search for in the search range.

  • search_result_array – A two-dimensional array, in which the first row or column will be searched for the search_key and the corresponding record in the last row or column will be returned.

Examples

LOOKUP(1234, A1:A10, B1:B10) and LOOKUP(1234, A1:B10) will both look for the value 1234 in the range A1:A10, and return the value from the corresponding record in the range B1:B10.

Notes

  • The arguments search_range and result_range must be one-dimensional, and the argument search_result_array must be two-dimensional.
  • If doing binary search over numeric or date values, make sure data in the lookup 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.
  • When using the search_result_array method, the returned value will come from the last row or column in the specified range. Use VLOOKUP or HLOOKUP to return a result from a different, specific row or column instead.
  • If the search key is not found, the record identified in the search range will be the value that is immediately smaller than the search key. For example, if the search range contains the values [1,3,5,6] and the search key is 2, the returned value will be that which corresponds to 1.