Advanced Excel Lookup Functions (9 Examples)

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.


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)
 

Vertical Advanced Lookup Based on Two Values

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


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)

HLOOKUP Function for Horizontal Lookup in Excel

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


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)

Advanced XLOOKUP Function to Lookup Horizontally and Vertically

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

Combination of INDEX and MATCH Functions for Advanced Lookup

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

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)

Use XLOOKUP Function with Multiple Criteria Lookup in Excel

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

OFFSET and MATCH Functions Combination for Advanced Lookup

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

Advanced Lookup with Excel VLOOKUP and CHOOSE Functions

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

Combination of VLOOKUP and MATCH Functions to Get Multiple Values in Excel

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

Application of Double VLOOKUP Functions

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


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.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo