Advanced Excel Lookup Functions (9 Examples)

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.

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.

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

Read More: How to Use COLUMN Function in Excel

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.

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

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

⏩ Note:

The XLOOKUP function is only available to Microsoft Excel 365.

Read More: DGET vs VLOOKUP in Excel

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.

• 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 1s and 0s.

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.

Read More: How to Use LOOKUP Function in Excel

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.

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

• 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}

Read More: How to Use SORT Function in Excel

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.

• 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}

Read More: How to Use Excel HYPERLINK Function

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.

• 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 Use Excel FORMULATEXT Function

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.

• 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 Use Excel UNIQUE Function

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 Articles

• How to Use FILTER Function in Excel 
  
• How to Use COLUMNS Function in Excel
• How to Use ROW Function in Excel
• How to Use ROWS Function in Excel
• How to Use ADDRESS Function in Excel
• How to Use INDIRECT Function in Excel