Site icon Professor Excel

LOOKUP Formula in Excel: Everything You Should Know

lookup, formula, excel

LOOKUP formula in Excel

Besides the well-known VLOOKUP formula, there is a similar function in Excel: LOOKUP. The formula is very unkown and hardly used. But there are good reasons why you should not use the function. Let’s start with the basics about the formula and then talk about why you should avoid it.

Before we start with the LOOKUP: If you consider using it in Excel – don’t! Rather take a look at the new XLOOKUP function. Or INDEX/MATCH. They usually work much better. That said: Let’s get started!

Vector form of the LOOKUP function

Structure of the function in the vector form

The LOOKUP formula is available in two forms, a vector form and an array form. Whether you use the vector or array form of the formula is determined by the size of array and the number of arguments.

Structure of the LOOKUP formula in the vector form

The LOOKUP formula in the vector form has two arguments and one optional third argument. The structure of the formula looks like this:

  1. The lookup value is the value you search for. As usual, it can be a number, text, logical value, name or cell reference.
  2. The lookup vector defines the cell range you search the lookup value (1) in. In the vector form it can only be a one-dimensional cell range. That means either a cell range within one row or within one column.
  3. The result vector is optional. If you want to return a value from another column or row than the lookup vector, you can define it here.

Curiosities about the function

Example for the LOOKUP formula in the vector form with different vectors

There are some curiosities about the LOOKUP formula:


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...

Boost your Excel skills: Learn the best Excel tricks and tutorials!


Example for the LOOKUP function in the vector form

Example for the vector form of the LOOKUP formula

The picture on the right-hand side shows an example for the vector form of the LOOKUP formula.

  1. The first argument is the lookup value and in the example, it refers to cell B12. Cell B12 has the car type “Audi A3”.
  2. The second part of the LOOKUP formula defines the lookup vector. In this case, it’s the cell range B3 to B8.
  3. The third and last argument of the vector form of LOOKUP is the result vector. In this example, it’s the cell range C3 to C8.

In summary, you search for “Audi A3” in the cell range B3 to B8. If found, the corresponding value from the cell range C3 to C8 will be returned.

Which value will be returned in this case? The result is 21,000. Why not 20,000 as it’s the return value to the first instance of “Audi A3”? Because of the curiosity of the LOOKUP formula that it returns the from the last instance the search value is found. And that’s the case in cell B5. But careful: If cell B8 also contains “Audi A3”, still 21,000 will be returned. That’s due to the last curiosity, that the lookup vector must be sorted in ascending order.

 

 

Array form of the LOOKUP function

Structure of the function in the array form

Structure of the LOOKUP formula in the array form

The array form of the formula only has two arguments unlike the vector form, which has an additional, optional third argument.

The structure of the function in the array form:

  1. The lookup value is, like in the vector form, the value (number, text, etc.) you search for.
  2. The array encloses both: The search row or column and the return row or column. Why row or column? Because the shape of the array defines if the LOOKUP formula searches vertically or horizontally. If the array has more rows than columns, it searches vertically. If the array has more columns than rows the other way: it searches horizontally.

How does Excel know, which column (or row) to return as there are only two arguments? It’s quite straight forward: If your LOOKUP formula works vertically (that means your array has more rows than columns) it searches the lookup value in the leftmost column. It returns the value from the rightmost column in your array.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Example for the LOOKUP formula in the array form

Example for the array form of the LOOKUP formula

The image on the right-hand side shows an example for the array form of the LOOKUP formula. The formula in cell F12 has two arguments:

  1. The search value is provided in cell E12. In this case, you search for “Audi A3”.
  2. The array is the cell range E3 to F8. That is the highlighted range of cells above. Because this range has more rows than columns, the LOOKUP formula works vertically. It searches in the leftmost column of this range, which is the column E. It returns the value from the rightmost column in this cell range which is column F.

What is the result of the formula? Like in the example of the vector form of the formula it’s 21,000. That is the corresponding value of the last time Excel finds “Audi A3” in the cell range E3 to E8.

Please note: If your array is square (that means it has the same number of rows and columns), it works vertically.

 

 

Determine the form of the function

 

 

Troubleshooting: Typical errors

If your LOOKUP formula doesn’t return the expected result, please check one of the following typical errors.

Recommendation: Use INDEX/MATCH or VLOOKUP/HLOOKUP instead.

 

 

Why you should avoid the LOOKUP formula

Exit mobile version