In this tutorial, I will discuss several methods regarding **MS Excel** 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.

**Table of Contents**hide

**Download Practice Workbook**

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

**9 Examples of Advanced Excel Lookup Functions**

**1. 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**of the state like**Los Angeles**.

**Read More:** **Excel LOOKUP vs VLOOKUP: With 3 Examples**

**2. 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 lookup 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:** **7 Types of Lookup You Can Use in Excel**

**3. 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 of
**Maryland**:**Baltimore**.

**⏩ Note:**

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

**4. Combination of 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**,**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 have 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 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. Use 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 for 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 in a similar way 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**}

**Read More:** **How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)**

**6. OFFSET and MATCH Functions Combination 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. 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. Combination of 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**.

**Read More:** **How to Lookup Multiple Values in Excel (10 Ways)**

**9. Application of 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 of 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**.

**Read More:** **How to Lookup a Table in Excel (8 Methods)**

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