7 Types of Lookup You Can Use in Excel

One of the frequent uses of Excel is looking up for a value in a table or data range. In Excel, we have 4 dedicated lookup functions. Besides that, we can apply a combination of different functions to look up for value. We can use 7 types of lookup in Excel to find out specific values.

In this 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 7 different types of Lookup.

dataset


Download Practice Workbook


7 Types of Lookup in Excel

1. 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.

➤ 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 will give the return from the result array.

lookup

➤ Press ENTER

As a result, 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.

types of lookup in excel

Read More: Advanced Excel Lookup Functions (9 Examples)


2. HLOOKUP Function

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.

➤ Type the following formula,

=HLOOKUP(K9,B4:G14,8,FALSE)

Here, K9 is the lookup value, B4:G14 is the table array, 8 is the row index number that means we want the value from the 8th row of the table and FALSE indicates that the function will lookup for an exact match. The formula will lookup for K9 in the top row of the table B4:G14 and will return the value from the 8th row of the column in which K9 has been found.

hlookup

➤ Press ENTER

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

types of lookup in excel


3. Excel 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 using the VLOOKUP function.

➤ Insert the following formula,

=VLOOKUP(K7,B4:I14,8,FALSE)

Here, K7 is the lookup value, B4: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. The function will lookup for K7 in the leftmost column of the table B4:I14 and will return the value of the 8th column from the row in which the lookup value has been found.

vlookup

➤ Press ENTER

As a result, you will get the grade of that student (cell K7).

types of lookup in excel

Read More: Excel LOOKUP vs VLOOKUP: With 3 Examples


4. Excel XLOOKUP Function

The XLOOKUP function is the most powerful function in Excel to lookup for a value from a range. This function is only available in Office 365. 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.

➤ Type the following formula to get the grade of one of the students

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

Here, the formula will lookup for the value K7 in the range B5:B14 and will return the corresponding value from the range I5:I14

xlookup

➤ Press ENTER

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

types of lookup in excel


Similar Readings


5. OFFSET and MATCH Functions to Lookup Data

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 for a value.

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

➤ Type the following formula

=OFFSET(B4,MATCH(K7,B5:B14,0),MATCH(K6,C4:I4,0))

Here, B4 is the reference cell, which is the first cell of our dataset, K7 is the name of the student, B5:B14 is the range where the name of the student will be matched, K6 is the value which 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. The formula will give the value from the intersecting cell of Marsal (name of the student) row and Grade column.

OFFSET MATCH

➤ Now, Press ENTER

As a result, you will get the Grade of the student Marsal.

types of lookup in excel


6. INDEX and MATCH Functions Lookup

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.

➤ Type the following formula to find out the grade of a student,

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

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

INDEX MATCH

After inserting the formula,

➤ Press ENTER.

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

types of lookup in excel


7. Excel XMATCH Function to LOOKUP

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 for a value. Let’s find out the grade of a student from our dataset using the XMATCH function.

➤ Type the following formula,

=INDEX(B4:I14,XMATCH(K7,B4:B14),XMATCH(K6,B4:I4))

Here the XMATCH function will give the position of K7 from the range  B4:B14 and the position of K6 from the range B4:I4. Now the INDEX function will use the position of K7 as the row number and the position of K6 as the column number in table B4:I14 to return the value of the cell at the intersection of that row and column.

XMATCH

➤ Press ENTER

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

types of lookup in excel


Conclusion

You can lookup for values in Excel by using any of the 7 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

Prantick

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