Excel LOOKUP() function

Excel offers a function named LOOKUP() function to find an approximation match of data. It performs a rough match lookup either in one-row or one-column range. However, the LOOKUP() function searches for a value itself or its approximate match within a range of column/row.

For example, The LOOKUP() function is useful when you don't have the exact value you are looking for. So, we use its nearby value to find the exact result that we want. The LOOKUP() function will help us to search for its exact or nearby match within a range.

In MS Excel, this function is categorized under the Lookup and Reference functions inside the formula bar. You can access it from the Formula tab and provide the values to their respective field in its user interface. You can also use it directly in the formula bar by its syntax. Besides this, you can use it many other uses.

Need of LOOKUP() function

Let's take a scenario to understand the need for LOOKUP() function in Excel. For example, we have a worksheet containing a list of product id, product name, and price. These columns are containing a lot of data.

A user has a requirement to find the product name, whose price is 829 or around it. LOOKUP() function will help us to achieve this result. We will use the LOOKUP() function to find the product name for this value.

Syntax

This function has very different parameter values in which the first two are essential and the third one is optional.

On some Excel tutorial websites, you might get one more syntax for the LOOKUP() function. First syntax is given above and another one is -

You can consider this syntax same as syntax1 without [result_vertor] parameter.

Parameters

The Lookup() function consists of three parameters and all three are important to look for the value and return the result.

Lookup_value - The first parameter of this function is lookup_value that holds the value for which the user is searching within the lookup_range.

Lookup_range or array - The lookup_range parameter holds the range of cells in which we look for the lookup_value. These range of cells can be either one-row or one-column.

[result_vector] (Optional parameter) - The result_vector is the most important but optional parameter of this function. It also consists of a range of cells corresponding to the lookup_range.

When the nearby match (approximate match) is found within the lookup_range, the LOOKUP() function picks the adjacent value from result_vector and returns it back to the users.

Return value

The LOOKUP() function returns any type of data depends on the parameter passed in LOOKUP() function. It can be a number or string.

Additional result:

If the LOOKUP() function is unable to find the exact match for the value (lookup_value) we are searching for, it returns the largest value from the lookup_range that is less than or equal to the value.

What is the use of result_vector?

As we know that the [result_vector] is an optional parameter, it's your choice that you will use it or not.

• If you provide the result_vertor parameter in the LOOKUP() function, the resultant value is selected from this range and returned to the user on finding the exact or approximate match.
• If the [result_vertor] parameter is not provided to the LOOKUP() function, it returns the approximate match value itself that found inside the lookup_range parameter.

To understand the LOOKUP() function, it is important to learn with example, to know the practical working. We will later understand the usage of this parameter below in this chapter with the help of examples.

How to use LOOKUP() function?

We have several examples to learn the LOOKUP() function by providing different parameter values. These examples will help you learn it better. We will use both syntaxes to see what values they will return when applying on Excel data.

Example 1: With [result_vector] parameter

We have a worksheet containing a list of product id, product name, and price. A user wants to find the product name, whose price is 2589 or around it. We will use LOOKUP() function to find the product name for this value.

Let's see how we can achieve this result using the LOOKUP() function and how it will help us to achieve this result.

Steps to use LOOKUP() function

1. Write the following LOOKUP() formula in a cell where you want to get the result.
   =LOOKUP(2589,C2:C7,B2:B7)
   
2. Now, get the result with a single click now by pressing the Enter key on your keyboard. See the resulting product name.
   
   The LOOKUP() function found 2589 near to 2549, which is the price of Speaker. Thus, it returned Speaker as the resultant value.

Example 2: Without using [result_vector] parameter

In case we don't use the third parameter inside the LOOKUP() function, it is a big question that what it will return to the user on finding approximate value.

We will use the same values used in the above example so that you can compare both results.

1. Write the LOOKUP() formula without using [result_vector] value, like this -
   =LOOKUP(1289, C2:C7)
   
2. Now, hit the Enter key to get the result for this formula. See that it has returned value 2549, which is corresponding to the Speaker.
   
   So, I hope you understood the working and usage of [result_vector] inside the LOOKUP() function how result_vector affects the results.

Problem with LOOKUP() function

You may face the problems with LOOKUP() function while using it on Excel data. Sometimes, it does not return the nearest approximate value to the lookup_value. Let's see with an example which type of problem you may face with LOOK() function.

1. We are looking for a product whose price is 999 approximate to it. Use the LOOKUP() formula in this way:
   =LOOKUP(999,C2:C7,B2:B7)
   
2. Now, hit the Enter key to get the result for this formula. See that it has returned the product named Bluetooth, whose price is 620.
   
   You will notice that the working having a product of price 999 and our lookup_value is also 999, but it still returned Bluetooth.
   Now, a big question is - if we are looking for the product whose price is approximate to 989. But it is returning Speaker as the resultant value which price is 620. However, Wireless is the correct result for this whose price is 999 nearest approximate value to 989.

Cause and Solution

There can be two reasons for returning the incorrect value:

1. Sometimes, LOOKUP() function returns an incorrect value if values of lookup_range parameter are not sorted in ascending order. LOOKUP() treat the columns are sorted and chooses the wrong value as a result. Hence, we will look for its solution.
2. Another reason is - you may get this type of error when you copy the formula from one cell to another cell. Cells reference changes and it may give the wrong value as result. There can be other reasons as well. So, avoid copying the formula.

Excel offers a function called VLOOKUP(). You can use it as an alternative to LOOKUP() function. It does not require lookup_range values to be sorted in ascending order.

Things to remember while using LOOKUP() function

Following are things you should remember while applying the LOOKUP() formula on Excel data.

1. #N/A Error - You may get this error when LOOKUP() function fails to find the closest match of the value you are looking for. This can be because of -
   • The smallest value inside the lookup_range parameter is greater than the lookup_value.
   • The lookup_range values are not in ascending order.
2. #REF Error - This error occurs when the formula looks for a cell reference that does not exist. This can be because of -
   • You have created formula earlier, and the cells are deleted after the LOOKUP() is entered.
   • When you copied the function from one cell to another cell, formula becomes invalid and generates this error.

Hence, you should avoid them while working on Excel data and using the LOOKUP() formula.

Verify different resultant values

Based on this Excel spreadsheet, we will analyze the results for the different lookup_value in LOOKUP() function.