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.

**Table of Contents**Expand

## 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.**

**Remember**: This function is only available in

**.**

*Microsoft Excel 365***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**