LOOKUP Formula in Excel

LOOKUP Formula in Excel (Table of Content)

• LOOKUP Formula in Excel
• How to Use LOOKUP Formula in Excel?

LOOKUP Formula in Excel

The LOOKUP formula finds the first column or first row of a table or an array and gives a value from any cell on the same row or the same column of the table or array respectively.

Basically, in simple words, this formula helps to search any data from a range of cells or array or a table based on its search value. Very quickly and accurately.

Implementation of the LOOKUP Formula

We can implement the LOOKUP Formula in 2 methods.

Method 1

The formula of the LOOKUP function is:

=LOOKUP (lookup_ value, lookup_ vector, [ result_ vector ] )

The arguments of this function are explained in the following table.

Argument	Explanation
Lookup _ Value	It is the search value that is found in the 1st column or the 1st row of the table array. It can either be a text or a Cell Reference.
Lookup _ Vector	It is the 1st row or the 1st column of the data table where search value can be found
Result _ Vector	It is the result row or the result column of the data table where the result searched against the Lookup _ Value, can be found

Method 2

The formula of the LOOKUP function is:

=LOOKUP (lookup_ value, table_ array)

The arguments of this function are explained in the following table.

Argument	Description
lookup_value	It is the search value that is found in the 1st row of the table array. It can either be a text or a Cell Reference.
table_array	It is the table or array where the result, as well as the search value, is recorded. But in this case, the result will be always from the last column or the last Row of the selected data range based on the vertical or horizontal table respectively. The 1st column or the 1st row of the selected data range based on the vertical or horizontal table respectively will always be that column or row which contains the Lookup _ Value.

How to Use LOOKUP Formula in Excel?

We will discuss 2 examples of the LOOKUP formula. 1^st one is for the data in a Vertical Array and the later one is for the data in a Horizontal Array.

Example #1

To know about the LOOKUP function, let us take one example.

Tony Stark has a flower shop with a large variety of flowers. Whenever a customer buys a flower, Tony needs to check the price of the flower by looking at every entry in the flower price list.

This wastes a lot of time, as he is required to go through the entire list.

Therefore, he decides to use MS Excel to check the price of the flower.

Tony enters the name of all the flowers in the 1^st column. In the 2^nd column, he enters the price of each flower.

 Now he has a list of flowers with their respective prices.

If Tony wants to find the price of any particular flower, he can use the Lookup Function.

He enters the name of the flower in the cell D4. Let us take the name as Daisy.

Then in cell E4, he adds LOOKUP function, =LOOKUP (D4, A2:B70).

Where,

D4 is the cell, which contains the flower name.
A1:B70 is the range, which shall be checked for the flower name and the flower price.

Therefore the price of the entered flower is displayed.

This can also be done in another way as mentioned earlier.

He enters the name of the flower in cell G4. Let us take the name as Daisy.

Then in H4 cell, he adds LOOKUP function,

=LOOKUP (G4,A2:A70, B2:B70).

Where,

G4 is the cell, which contains the flower name.
A2:A70 is the range, which shall be checked for the flower name.
B2:B70 is the range, which shall be checked for the price of the flower.

Therefore the price of the entered flower is displayed.

This saves time and effort of Tony.

Example #2

Let us consider the scenario of GUIDING SOLUTION COMPANY. As per the company’s policy, the annual percentage hike in salaries of the employees will be based on the performance grade. The employee details such as Employee name, Salary, and Grade is recorded in an Excel worksheet.

 

 The increment applicable to the employee is required to be fetched from the incremental details table in the same worksheet, which contains details about the grade and its respective percentage of the hike.

So now the HR, Steve Rogers, wants to find the Hike Percentage of each employee with the help of HLOOKUP function. In addition, Steve wants to find out the new salary of each employee.

To calculate the hike Percentage, Steve needs to add the LOOKUP function in the E2 cell,
=LOOKUP(D2,$I$2:$L$3)
Where,

D2 is the cell, which contains Grade of each employee
$I$2:$L$3 is the range, which shall be checked for Grade and the Hike Percentage

 

Or Steve can use another way by adding the LOOKUP function in the E2 cell,
=LOOKUP(D2,$I$2:$L$2,$I$3:$L$3)

Where,

D2 is the cell, which contains the Grade of each employee.
$I$2:$L$2 is the range, which shall be checked for the Grade
$I$3:$L$3 is the range, which shall be checked for the Hike Percentage.

Therefore, the Hike percentage will get displayed. Then, Steve must drag down the formula to get all the hike percentages of employees according to their grades.

Now Steve needs to find out the new salary after the hike. To accomplish that he needs to add the required formula, =C2+C2*E2.

Where,

C2 is the salary of each employee.
E2 is the Hike percentage based on their Grade

Therefore, the new salary will get displayed. Then, Steve must drag down the formula to get the new salaries of all the employees after the hike according to their grades.

Things to Remember

• If lookup_value to be selected occurs more than once, then the LOOKUP function will return the first instance of lookup_ value.
• Lookup_Vector or the 1^st column or 1st row of the table array must be in ascending order.

Recommended Articles

This has been a guide to LOOKUP Formula in Excel Here we have discussed How to Use LOOKUP Formula in Excel along with examples and downloadable excel template. You may also look at these useful excel articles –

1. VLOOKUP Function in Excel
2. Excel RATE Formula
3. Excel COLUMN to Number
4. Calculate Running Total in Excel