LOOKUP function

LOOKUP returns the contents of a cell either from a one-row or one-column range or from an array.


Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, the search and result vector can be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted, otherwise the search does not return any usable results. The search supports regular expressions.

After you have entered text, you can enter all* to find the first location of all followed by any characters.

LOOKUP(search criterion, search vector, result_vector)

  • search criterion is the value to be searched for; entered either directly or as a reference.
  • search vector is the single-row or single-column area to be searched.
  • result_vector is another single-row or single-column range from which the result of the function is taken. The result is the cell of the result vector with the same index as the instance found in the search vector.


=LOOKUP(A1,D1:D100,F1:F100) searches the corresponding cells in range D1:D100 for the number that you entered in cell A1. For the instance found, the index is determined: for example, the twelfth cell in this range. Then, the contents of the twelfth cell are returned as the value of the function in the result vector.