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.

**Table of Contents**hide

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

âž¤ 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.

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

âž¤ Press **ENTER**

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

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

âž¤ Press **ENTER**

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

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

âž¤ Press **ENTER**

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

**Similar Readings**

**How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)****Use Excel ADDRESS Function (7 Examples)****How to Lookup Text in Excel (7 Suitable Methods)**

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

âž¤ Now, Press **ENTER**

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

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

After inserting the formula,

âž¤ Press **ENTER**.

As a result, you will get the grade of the student youâ€™ve mentioned in cell **K7**.

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

âž¤ Press **ENTER**

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

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