Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Different Types of Lookup to Apply in Excel (8 Types)

Certainly, one of the frequent uses of Excel is looking up a value in a table or data range. Now, Excel contains a couple of dedicated lookup functions, in addition to those, we can combine different functions to lookup a value. In fact, in this article, we’ll explore 8 types of lookup in Excel to find out specific values.

Before jumping into the next part, let’s have a quick look at the functions and formulas we are using to show different types of lookups in Excel.

Overview of the 8 types of lookup in Excel showing the formula and the output


Download Practice Workbook


What Is Lookup in Excel?

In simple terms, lookup means looking for a specific value within a row or a column in Excel after meeting specific criteria. You can look for single or multiple values within a range. There are several specific Excel built-in functions for looking up value both horizontally and vertically. Moreover, you can also combine multiple functions for performing a lookup.


8 Types of Lookup in Excel

In the following dataset, we have a list of marks obtained by different students throughout a semester in a subject.

Dataset showing the student names, roll, marks in exams and grades

Now, we will use this dataset to demonstrate 8 different types of Lookup in Excel. Let’s demonstrate them with proper illustrations.


1. Using LOOKUP Function in Excel

The LOOKUP function looks up for a value in a given array and can return a result from another array. Suppose, we want to find out the grade of one of the students.

Steps:

➤ First, type the following formula,

=LOOKUP(K7,B5:B14,I5:I14)

Here, K7 is the lookup value, B5:B14 is the lookup array and I5:I14 is the result array. The LOOKUP function will find the lookup value in the lookup array and give the result array’s return.

Applying LOOKUP function to lookup the grade of a student

➤ Now, press ENTER

Hence, you will get the grade of that student. In a similar way, you can use the LOOKUP function to find out any specific value from a table.

Result showing the grade of the student by applying LOOKUP function 

Read More: Advanced Excel Lookup Functions (9 Examples)


2. Utilizing HLOOKUP Function for Horizontal Lookup

The HLOOKUP function looks for a value in the top row of a table or array and returns a value in the same column from a specified row. The function looks for a value along a row that means horizontally and that’s why it is called HLOOKUP.

Suppose, we want to find the mark obtained in the final by a student named Robin.

Steps:

➤ Here, apply the following formula to perform this task

=HLOOKUP(K5,B4:I14,8,FALSE)

Result showing the mark of the student by applying HLOOKUP function in Excel

🔎 Formula Breakdown

First of all, we can see K5 is the lookup value, B4:I14 is the table array, 8 is the row index number which means we want the value from the 8th row of the table and FALSE indicates that the function will lookup for an exact match. Then the formula will lookup for K5 in the top row of table B4:I14 and will return the value from the 8th row of the column in which K5 has been found.

Hence, you will get the mark in the final obtained by Robin.


3. Lookup Vertically with VLOOKUP Function

The VLOOKUP function is the complementary function of the HLOOKUP function. The HLOOKUP function looks for the value along a row while the VLOOKUP function looks for the value in a column. VLOOKUP looks for the value in the leftmost column and after that, it returns a value in the same row of the specified column.

Let’s find out the grade of one of the students (Sheldon) using the VLOOKUP function.

Steps:

➤ Here, insert the following formula for serving our purpose.

=VLOOKUP(K5,B5:I14,8,FALSE)

🔎 Formula Breakdown

At first, we can see K5 is the lookup value, B5:I14 is the table array, 8 is the column index number that means we want the value from the 8th column of the table and FALSE indicates that the function will lookup for an exact match. Then, the function will lookup for K5 in the leftmost column of the table B5:I14 and will return the value of the 8th column from the row in which the lookup value has been found.

Hence, you will get the grade of that student (cell K5).

Result showing the grade of the selected student by using VLOOKUP function in Excel

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples


4. Applying XLOOKUP Function to Lookup Value from a Range

The XLOOKUP function is the most powerful function in Excel to lookup for a value from a range. So if you use an older version you won’t be able to use this function. The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array.

Steps:

➤ Here, in cell L5, apply the following formula to get the grade of one of the students

=XLOOKUP(K5,B5:B14,I5:I14)

🔎 Formula Breakdown

In the formula will lookup for the value K5 in the range B5:B14 and will return the corresponding value from the range I5:I14

Hence, you will get the grade of the student mentioned in cell K5.

Looking up the grade of the chosen student with XLOOKUP function

Remember: This function is only available in Microsoft Excel 365.

Similar Readings


5. Implementing OFFSET and MATCH Functions

We can look up for a value using the OFFSET function and the MATCH function altogether.

The OFFSET function returns a reference to a range. The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order. Now, let’s see how we can use these two functions altogether to look up a value.

We will find out the grade of a student from the dataset.

Steps:

➤ First apply the following formula and then press ENTER to get the result.

=OFFSET(B4,MATCH(K5,B5:B14,0),MATCH(K4,C4:I4,0))

🔎 Formula Breakdown

Here, B4 is the reference cell, which is the first cell of our dataset, K5 is the name of the student, B5:B14 is the range where the name of the student will be matched, K4 is the value that we are searching for i.e Grade, C4:I4 is the range from where the column of Grade will be matched. 0 is used to refer to an exact match. Then the formula will give the value from the intersecting cell of Marsal (name of the student) row and Grade column.

Hence, you will get the Grade of the student Marsal.

Implementing OFFSET-MATCH functions to lookup the grade corresponding to the matched student in Excel


6. Lookup Value with INDEX and MATCH Functions

The INDEX function returns the value or reference of the cell at the intersection of a particular row and column in a given range. So we can use the INDEX function with the MATCH function to lookup for a value from a table.

Steps:

➤ The first and last thing you have to do here is to apply the following formula to find out the grade of a student,

=INDEX(I5:I14,MATCH(K5,B5:B14,0))

🔎 Formula Breakdown

Here, I5:I14 is the array from where the resulting value will be found, K5 is the lookup value, B5:B14 is the lookup array, 0 indicates an Exact match. Then MATCH function will return the position of the lookup value K5 

MATCH(K5,B5:B14,0) => 5

Next, the INDEX function will return the corresponding value from the I5:I14 array.

INDEX(I5:I14,MATCH(K5,B5:B14,0)) = =INDEX(I5:I14,5)

As a result, you will get the grade of the student you’ve mentioned in cell K5.

Result of INDEX-MATCH functions returning the grade of the selected student in Excel


7. Employing XMATCH Function to LOOKUP Value

The XMATCH function returns the relative position of an item in an array. So instead of using the MATCH function, we can use the XMATCH function to look up a value. Let’s find out the grade of a student from our dataset using the XMATCH function.

Steps:

➤ Here, apply the following formula in a cell where you want to get the output:

=INDEX(B5:I14,XMATCH(K5,B5:B14),XMATCH(K4,B4:I4))

🔎 Formula Breakdown

First of all, illustrate the XMATCH function. According to the definition, the XMATCH function will give the position of K5 from the range  B5:B14 and the position of K4 from the range B4:I4.

So, XMATCH(K5,B5:B14) => 4

And XMATCH(K4,B4:I4) => 8

Now the INDEX function will use the position of K5 as the row number and the position of K4 as the column number in table B5:I14 to return the value of the cell at the intersection of that row and column.

INDEX(B5:I14,XMATCH(K5,B5:B14),XMATCH(K4,B4:I4)) = INDEX(B5:I14,4,8) = 8

As a result, you will get the grade of the student mentioned in cell K5.

Lookup grade of a selected student with INDEX and XMATCH functions in Excel


8. Combining VLOOKUP and CHOOSE Functions for Lookup in Excel

In this method, we will use the CHOOSE function nested in the VLOOKUP function. The CHOOSE function chooses a value or action to perform from a list of values based on an index number. Almost all the time we are used to lookup a value from left to right. But this formula will let us extract a value from the right to the left column.

Steps:

➤ First and last task you have to do here is to apply the following formula,

=VLOOKUP(K5,CHOOSE({1,2},I5:I14,B5:B14),2,0)

🔎 Formula Breakdown

Here, the CHOOSE function will look for the corresponding data of column I in column B and return an array of these two data.

CHOOSE({1,2},I5:I14,B5:B14) => {“A”,”Harold”;”A+”,”Sheldon”;”A-“,”Jessy”;”B”,”Penny”;”B-“,”Howard”;”B-“,”Lily”;”B+”,”Robin”;”A-“,”Marsal”;”A-“,”Peter”;”B+”,”Berny”}

Then, the VLOOKUP function will look for the cell value of K5 in that array and return the second value (column index 2) from that array which matches K5.

VLOOKUP(K5,CHOOSE({1,2},I5:I14,B5:B14),2,0) = VLOOKUP(K5,{“A”,”Harold”;”A+”,”Sheldon”;”A-“,”Jessy”;”B”,”Penny”;”B-“,”Howard”;”B-“,”Lily”;”B+”,”Robin”;”A-“,”Marsal”;”A-“,”Peter”;”B+”,”Berny”},2,0) = Penny

Finally, the Output will be => Penny

Lookup grade from right column and extract student name from left with VLOOKUP and CHOOSE functions in Excel


Conclusion

You can lookup for values in Excel by using any of the 8 types described in this article. If you face any kind of confusion about any of the types of lookup in Excel please leave a comment.


Related Articles

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo