In this tutorial, I will discuss several methods regarding MS Excel’s advanced Lookup functions. I will use the single Lookup Function to return expected values. Besides, I will show some combinations of functions to show the advanced application of Lookup functions in Excel.

**Table of Contents**Expand

## 1. Doing Vertical Advanced Lookup Based on Two Values

First, we will see the application of **the VLOOKUP function**. Usually, this function looks up values vertically, which means it will search for a column. Suppose, we have a dataset containing US states with their respective postal abbreviation and largest cities. Now, if you want to look for the Largest City for a State depending on two criteria (state names and abbreviation) then the usual **VLOOKUP** formula won’t work. In such cases, we will add a helper column to the main dataset (to the leftmost side). This helper column will contain two criteria concatenated. After that, we will put both criteria in the **VLOOKUP** formula as below.

**Steps:**

- First, type the below formula in
**Cell C16**.

`=VLOOKUP(C14&" "&C15,B5:E12,4,FALSE)`

** **

- As a result, for the State California and Abbreviation CA, we got the Largest City in the state like Los Angeles.

## 2. Using HLOOKUP Function for Horizontal Lookup in Excel

Now, we will see **the HLOOKUP function**. **HLOOKUP** stands for horizontal lookup. This function looks for a value in a data range and returns the corresponding value for a specified row. For instance, I will now look up the state name in row 3.

**Steps:**

- Write the below formula in
**Cell C15**.

`=HLOOKUP(B15,B4:D12,C14,0)`

** **

- Consequently, we got the State in row 3, which is Texas.

⏩ **Note:**

Make sure you include the header rows of the dataset in the **HLOOKUP** formula.

**Read More: **How to Use VLOOKUP and HLOOKUP Combined Formula in Excel

## 3. Implementing Advanced XLOOKUP Function to Lookup Horizontally and Vertically

In this method, we will see how **the XLOOKUP function** works. This is also an advanced lookup function in Excel. It looks for certain information in a range/array of values and returns the corresponding values from the 2nd range/array. For instance, we will look for the state of **Maryland** from the range** B5:B11** and eventually get the corresponding largest city from the next range.

**Steps:**

- Type the below formula in
**Cell C15**.

`=XLOOKUP(C14,B5:B12,C5:C12)`

** **

- As a consequence, here we get the largest city in
**Maryland**:**Baltimore**.

**⏩ Note:**

**The XLOOKUP function** is only available to Microsoft Excel 365.

## 4. Combining INDEX and MATCH Functions for Advanced Lookup

Till now, we have seen the application of a single lookup function. However, now we will use the combination of** INDEX** and **MATCH **functions to see the advanced application. By using this combination of functions, you can look up a value dependent on multiple criteria. Suppose, we have a dataset that contains the state-wise population of the **USA**. Now we will fetch the population of a city based on 3 criteria:** State**, **Abbreviation**, **Largest City**.

**Steps:**

- Type the following formula in
**Cell C16**.

`=INDEX(E5:E11,MATCH(1,(C13=B5:B11)*(C14=C5:C11)*(C15=D5:D11),0))`

** **

- Here, we have received the population for
**California**,**CA**, and**Los Angeles**.

**🔎 How Does the Formula Work?**

This formula is a bit tricky. Let’s explain it below.

The first part of the formula is:

➤ **MATCH(1,(C13=B5:B11)*(C14=C5:C11)*(C15=D5:D11),0)**

To understand this formula, let’s take a look at the syntax of **the MATCH function**.

**MATCH(lookup_value, lookup_array, [match_type])**

In our formula,

**lookup_value**:** 1**

**lookup_array**:** (C13=B5:B11)*(C14=C5:C11)*(C15=D5:D11)**

**match_type**:** 0**

The above formula will look like below upon calculation:

**MATCH(1,({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}),0)**

Now, multiplication converts **TRUE** and **FALSE **to **1**s and **0**s.

After the multiplication, the formula will look like the below:

**MATCH(1{1;0;0;0;0;0;0},0))**

The above formula will return {**1**}.

The final stage of the formula is:

➤ **INDEX(****MATCH(1,(C13=B5:B11)*(C14=C5:C11)*(C15=D5:D11),0))**

Upon calculation, this part of the formula will look like below

**INDEX(E5:E11,1)**

Finally, the formula will return the expected population as below

{**3,979,576**}

⏩ **Note**:

In all versions of Excel, except Excel 365, press** Ctrl + Shift + Enter** to enter the formula as an array.

## 5. Using XLOOKUP Function with Multiple Criteria Lookup in Excel

Likewise, **Method 4**, we will use **the XLOOKUP function** to look up information depending on multiple criteria. This time we will look at the population of *Arizona, AZ, Phoenix.*

**Steps:**

- Type the below formula in
**Cell C16**.

`=XLOOKUP(1, (C13=B5:B11)*(C14=C5:C11)*(C15=D5:D11),E5:E11)`

** **

- Hit
**Ctrl + Shift + Enter**to enter the formula as an array (Except in Excel 365). - Consequently, we will get the
*Population,*based on the 3 specified criteria.

**🔎 How Does the Formula Work?**

**➤ XLOOKUP(1, (C13=B5:B11)*(C14=C5:C11)*(C15=D5:D11),E5:E11)**

Part of this formula works similarly to the formula of **Method 4**, except here, we have used **the XLOOKUP function** instead of the **INDEX**–**MATCH** combination.

Eventually, the above formula will look like this:

**XLOOKUP(1,{0;0;1;0;0;0;0},E5:E11)**

Finally, the following will be the population:

{**1,680,992**}

## 6. Combining OFFSET and MATCH Functions for Advanced Lookup

Now, we will use the combination of **OFFSET **and **MATCH** functions to look for a specific value. This time, we will look for a population value based on two criteria, **State**: **Arizona**, and **Heading**:** Largest City**.

**Steps:**

- Type the below formula in
**Cell 15**.

`=OFFSET(B4,MATCH(C13,B5:B11,0),MATCH(C14,B4:E4,0))`

** **

- As a result, we will get the below result.

**🔎 How Does the Formula Work?**

**➤ MATCH(C13,B5:B11,0)**

This part will match the state name in the range **B5:B11**, and return the position of the state, which is **3**.

**➤ MATCH(C14,B4:E4,0)**

Next, this part of the formula will match the heading in the range **B4:E4** and return the position **3**.

**➤ OFFSET(B4,3,3)**

Lastly, this part of the formula will return the population for the specified criteria, as below.

{**1,680,992**}

## 7. Doing Advanced Lookup with Excel VLOOKUP and CHOOSE Functions

In this method, we will learn the advanced application of the **VLOOKUP** and **CHOOSE** functions.

**Steps:**

- Type the formula in
**Cell C14**.

`=VLOOKUP(C13,CHOOSE({1,2},B5:B11,D5:D11),2,FALSE)`

- As a result, we will get
*Houston,*the largest city in Texas.

**🔎 How Does the Formula Work?**

**➤ CHOOSE({1,2},B5:B11,D5:D11)**

This part of the formula will create an array, having first-column values from **B5:B11**, and second-column values from **D5:D11**. The array will look like this:

{“**California”,”Los Angeles”;”Texas”,”Houston”;”Arizona”,”Phoenix”;”Alabama”,”Birmingham”;”Maryland”,”Baltimore”;”Luisiana”,”New Orleans”;”Missouri”,”Kansas City”**}

**➤ VLOOKUP(C13,CHOOSE({1,2},B5:B11,D5:D11),2,FALSE)**

The final result is:

{**Houston**}

## 8. Combining VLOOKUP and MATCH Functions to Get Multiple Values in Excel

Now, we will explain the application of **VLOOKUP **and **MATCH** functions. Here, we have a dataset containing monthly sales of several grocery items. Now we will look for the sale of** Feb **for **Ramen**.

**Steps**:

- Type the below formula in
**Cell C15**.

`=VLOOKUP(C13, B5:E11, MATCH(C14, B4:E4, 0), FALSE)`

- As a result, here we got the
**Feb**sales of**Ramen**which is**$2500**.

**🔎 How Does the Formula Work?**

**➤ MATCH(C14, B4:E4, 0)**

Here, the match function matches the value of **C14** in the range **B4:E11** and returns the position of the column, which is **3**.

**➤ VLOOKUP(C13, B5:E11, MATCH(C14, B4:E4, 0), FALSE)**

Finally, the **VLOOKUP** function looks for **Ramen **in column** 3** of range **B5:E11**.

## 9. Applying Double VLOOKUP Functions

Now, we will use the** double** **VLOOKUP** function formula. This is a very advanced lookup formula that is faster and saves time. For example, we have a dataset containing tables of patients’ records at a hospital. Now, by applying our first **VLOOKUP** we will look for the **Serial No.** of any patient to find the** ID** of that patient. Subsequently, we will apply another **VLOOKUP** to get the **Patient’s Name** through the **ID** we have received from the first **VLOOKUP**. So, let’s get through the steps.

**Steps:**

- First, write the following formula in
**Cell C5**.

`=VLOOKUP(VLOOKUP(B5,F9:H11,3,FALSE),B9:D11,2,FALSE)`

** **

- Consequently, you will get the
**Patient’s Name**regarding a particular**ID**.

**🔎 How Does the Formula Work?**

➤ **(VLOOKUP(B5,F9:H11,3,FALSE)**

Here, this part of the formula looks for the value of **B5 **in table **F9:H11** and returns the **ID** regarding the **Serial No.** of **B5**.

➤ **VLOOKUP(VLOOKUP(B5,F9:H11,3,FALSE),B9:D11,2,FALSE)**

Finally, this formula looks for the **ID** in Table **B9:D11** and returns the **Patient’s Name** corresponding to that **ID**.

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**Conclusion**

In the above article, I have tried to discuss the examples of Advanced Excel Lookup Functions elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

## Related Article

**<< Go Back to Excel LOOKUP and Reference Functions | Excel Function Categories | Excel Functions | Learn Excel**