Functions: LOOKUP

Let's start the discussion about 'Lookup & Reference' functions with LOOKUP function. Simply put LOOKUP function looks for a value either from one row range or one column range or from an array (array is set of rows and columns) and gives a corresponding value from same position in a second row or column. Continue to read below to see this function at length.

Excel LOOKUP function has two syntaxes.

LOOKUP(lookup_value,lookup_vector,[result_vector])

LOOKUP(lookup_value,array) 

First syntax is called 'vector' form and the latter one is 'array' form. 

Vector form

A vector is a one row or one column range. This form of LOOKUP searches for a value in the given row or column and returns corresponding value in another one row or one column range. Function syntax is below.

LOOKUP(lookup_value,lookup_vector,[result_vector])

lookup_value:  It is the value you want this function to search for. This can be a text, number, logical value, a cell address or a name.

lookup_vector: This is the place where this function should search for lookup_value. This range must be spread over one column OR one row only.

result_vector: This is the place where values you wish to retrieve are in. This range must be spread over one column OR one row only. Also should be of the same shape & size as lookup_vector. Its optional. If this is omitted, first column of data is considered.

Note 1: If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

Note 2: If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

Let's see this with an example: I have a table with set of numbers & names along with a colour assigned to each value as in below picture.

I tried LOOKUP with various formulas and see the results below.

Array form

Array form of this function is used when your data is in a tabular format and lookup_value is in the left most part of the table. This form searches lookup_value in the first column of given array and returns  corresponding value from the right most column in the same row. Function syntax is below.

LOOKUP(lookup_value,array) 

lookup_value: Same as vector form. What you are looking for.

array: Table form. If rows are more than columns, lookup_value is searched in first column. If more columns in the array then lookup_value will be searched in first row.

Important points to note:

1. Values in lookup_vector & array must be sorted in ascending order. If that's not done, LOOKUP may give incorrect result. If you cannot sort the data, consider using functions like VLOOKUP, HLOOKUP or MATCH.
2. For lookup_value, upper case and lower case text is equivalent. If you are searching for 'a' and lookup_vector contains 'A', result will be corresponding value of 'A'.
3. Result_Vector and lookup_vector must be identical in terms of shape & size.

Advantages of LOOKUP:

1. You can use LOOKUP function as an alternative to multiple IF functions.
2. You can return a value which is left side of lookup_value using vector form. Functions like VLOOKUP does not support this unless you tweak the standard function a bit.
3. This function supports looking values in rows and columns as well. In other functions either row wise or column wise search is possible (VLOOKUP for column search, HLOOKUP for row search)
4. Arguments need not be cell references. You can enter your own set of data in the formula itself. For example: Below function takes lookup_value from cell C2 and lookup in data in first set of curved brackets (i.e. A to I) and returns corresponding value from second set of curved brackets (1 to 9)

=LOOKUP(C2,{"A","B","C","D","E","F","G","H","I"}, {"1","2","3","4","5","6","7","8","9"})

Disadvantages of LOOKUP:

1. Since your data must be sorted first, this function is not flexible.
2. If lookup_value is not available in the list, LOOKUP considers next least number. This is not friendly if you are looking for exact match.

That's it about lookup. Post a comment if you have any doubts relating to this function or have anything to say. Happy learning!!