LOOKUP Function in Microsoft Excel

In contrast to the VLOOKUP and HLOOKUP functions in Excel, the LOOKUP function has two forms: vector and array. The following is a brief description of the differences in the two approaches.

Vector Form

Under the vector method, the formula you create will look for the cell you target, within a range of information (a single row or column) with the result you seek in some other range of information (another single row or column of same size as the first). Let us say hypothetically that you have a list of fruits whose sales prices vary by month, and you are curious as to the price of bananas in the month or March. We will assume that your list of fruit names is in column A beginning in row 3 with data through August (column I).


Thus, the entire range of data is within A3:I9. We would like to get the data for March, which, in this case is in column D. This means that the LOOKUP formula would be:

=LOOKUP("Banana",A3:A9,D3:D9), to yield the price per pound of bananas in March.

This would tell Excel to find the word "Banana" in the range of fruit, and whatever placement number within the column results, that same placement number will be used in the range for March to return the desired result. Similarly, if you wanted to find lychee values for August, the formula would be:

=LOOKUP("Lychee",A3:A9,I3:I9), to yield the price per pound of lychee in August.

To increase the efficiency of using this method, particularly if you have a lot of columns representing data for multiple years, is naming the columns. For example, if you name A3:A9 "Fruit_Range" and I3:I9 "August_Data" then the formula for the lychee example we just reviewed becomes:

=LOOKUP("Lychee",Fruit_Range,August_Data)

or

=LOOKUP(A8,Fruit_Range,August_Data), where the cell A8 contains the word lychee.

Why is this important? If you have a situation that requires looking up 1,000 fruit names across 36 months of data, the copying and pasting becomes more efficient when you need to create summary reports. This will be covered down the line with other articles, but just keep in mind that when you develop spreadsheets, efficiency and flexibility should be main drivers behind how you construct a financial model.

Array Form

The array form is very straightforward, in that you can either setup a long formula with specific references or highlight a set of data that comprises the range of information you are using. A simple example would be:

=LOOKUP("k",{"a","d","k","z";3,6,9,12}), which would find "k" in the array defined by "a","d","k" and "z" and return the appropriate value from the array defined as 3,6,9,12, and in this case, the answer would be 9. You could also have =LOOKUP("kangaroo",{"a","d","k","z";3,6,9,12}) and the result would still be nine. The point here is that if it is not a specific match, similar to VLOOKUP and HLOOKUP, Excel looks for the largest datapoint smaller than or equal to the reference value ("kangaroo", in this case). Finally, you could write this equation in the equivalent form of =LOOKUP("kangaroo",{"a",3;"d",6;"k",9;"z",12}). Note that the arrays could be flipped, to have Excel search a number lookup and return a letter or lookup a number to return another number or any assortment of combinations. As long as the arrays have the same number of values, you will not get an error message.

The other method I referenced relates to grabbing a range of data. For example, if you have colors listed in column A with unit sales data for four days in the subsequent columns, the entire range of data is comprised in the area defined by A2:E6.


If you wanted to know how many units were sold on day 4 of oranges (with "orange" in cell A6), you would enter the following formula:

=LOOKUP("orange",A2:E6) or =LOOKUP(A6,A2:E6), and the correct result would be displayed.

I have not used this form of the LOOKUP function, and it seems easily replaced by VLOOKUP. In addition, another drawback of this method is that if your column inputs (first column) are greater than the number of total columns you have in the data range, you will get a false answer (you can test this by creating a simple table as outlined above and adding a column "F" with data, changing the range to A2:F6 and you will get the answer "orange" instead of a number").

Another drawback to this method is a drawback of the vector form, too. There is no option to tell this formula to find the exact data point as in the case of VLOOKUP or HLOOKUP. There are ways around that, but for now, this standalone function will always find the closest value only.

Finally, either form requires the first column data to be in ascending order or the results may be correct. Based on the prior paragraph, you must make certain that the data is in ascending order or you will need to rely on another method to do the lookup you need.

While I do not use either of these functions extensively, the vector form is my preference if I have to use one or the other. Once you make the range reference absolute (the dollar signs in front of the letter and the number, like $A$2:$E$6), you can insert rows or cells and the values will maintain their correctness. The feature is something that I always consider when developing financial models.