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.
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.
Different Types of Lookup to Apply in Excel: 8 Types
In the following dataset, we have a list of marks obtained by different students throughout a semester in a subject.
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 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.
➤ 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.
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)
🔎 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 which 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 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).
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.
5. Implementing OFFSET and MATCH Functions
We can look up 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.
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.
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.
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
Download Practice Workbook
Conclusion
You can look 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.
<< Go Back to Lookup | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
