Google Sheets users are now familiar with XLOOKUP, so the question of whether to use it over other lookup functions is no longer relevant. Now is the time to learn advanced tips like nested XLOOKUP.

A nested XLOOKUP formula can help retrieve a value from multiple tables by looking through them one by one. But we can also use one table.

Syntax: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

If the nested XLOOKUP formula only involves one table, we usually use the second XLOOKUP formula either in the lookup_range or result_range.

However, if the nested XLOOKUP formula involves two tables, we will use the second formula in the search_key.

Let’s see how it works with three examples.

Nested XLOOKUP with One Table

Imagine you are a birdwatcher who has maintained a spreadsheet of your’s recent encounters with common and rarer species of birds.

The data structure is as follows: Weekday in A1:A, Bird Name in B1:B, and Number of Views in C1:C. We can use the following XLOOKUP to search for “Quail” and return the number of views.

=XLOOKUP("Quail",B2:B,C2:C)

It is easy to do because you know that the bird names are in the second column of the table, and the result is to be retrieved from the third column. However, what if you do not know which column contains the search key (bird name)? How would you apply the lookup then?

Example 1: Replacing Lookup_Range with a Dynamic Formula

Here comes the importance of naming columns with proper field labels. In the above example, the field labels in the top row are “Weekday,” “Bird Name,” and “Number of Views.”

The following XLOOKUP formula can dynamically find and return the lookup_range:

=XLOOKUP("Bird Name",A1:C1,A2:C)

How is this possible?

The above formula searches for the field label “Bird Name” in the first row of the table and returns all rows from the column that matches the field label.

Here, we have used one of the unique features of the XLOOKUP function. Unlike HLOOKUP, we do not need to specify the row index in XLOOKUP. Instead, we use the result_range.

In a horizontal lookup using XLOOKUP, if the result_range contains multiple rows, the formula will return all of those rows. That is the logic we applied.

Replace the range B2:B in the formula =XLOOKUP("Quail", B2:B, C2:C) with the above formula. This is our first nested XLOOKUP formula.

=XLOOKUP("Quail",XLOOKUP("Bird Name",A1:C1,A2:C),C2:C)

In the following illustration, the search_keys are specified in cells E2 and F2.

=XLOOKUP(F2,XLOOKUP(E2,A1:C1,A2:C),C2:C)

This is an example of nested XLOOKUP in Google Sheets.

What if I want to filter for a specific weekday and then apply the nested XLOOKUP formula?

This is an advanced nested or double XLOOKUP formula tip.

In real life, you may want to apply the nested XLOOKUP function to a specific part of a table. In that case, you can filter the rows before applying the function.

For example, if I have other weekdays in the “Weekday” column and want to use the nested XLOOKUP formula for “Monday,” I can use it as follows:

=LET(range,FILTER(A2:C,A2:A="Monday"),XLOOKUP(F2,XLOOKUP(E2,A1:C1,range),CHOOSECOLS(range,3)))

The FILTER function filters data in the range A2:C based on the value “Monday” in the first column.

The LET function allows us to assign a name to a range of cells. In this case, we are assigning the name “range” to the filtered range A2:C.

The CHOOSECOLS function returns a specified number of columns from a range of cells. In this case, we are returning the third column from the range “range”.

The rest of the formula components are the same as they were before.

Example 2: Replacing Result_Range with a Dynamic Formula

This time, we are using a different set of data. The data contains employee names in column A and their sales leads from January to April in columns B to D.

The following nested XLOOKUP function returns the February month sales leads of Jennifer.

=XLOOKUP(G2,A2:A,XLOOKUP(H2,A1:E1,A2:E))

In this formula, the search_keys are “Jennifer” and “Feb.” We want to look up the name “Jennifer” in column A and return the sales leads from the column named “Feb” dynamically.

Instead of specifying the range C2:C, we have used an inner XLOOKUP function to return the result_range dynamically.

The inner XLOOKUP function searches across the first row, A1:E1, for “Feb” and returns values from all rows from the matching column in the result range, A1:E1.

The outer function returns a value from this column.

Nested XLOOKUP with Two Tables

In this type of nested XLOOKUP, we will use the result of one XLOOKUP formula as the search_key in another XLOOKUP.

We will use one XLOOKUP in one table and the other XLOOKUP in another table.

Without further ado, let’s go to an example.

Example 1: Replacing Search_Key with an XLOOKUP

We have two tables in a Google Sheets file named “emp data” and “salary”. Here are the contents of each table:

The only common field in each table is “ID”. How do I retrieve the gross salary of one employee by using his name instead of his ID?

The following nested XLOOKUP formula will take care of that.

=XLOOKUP(XLOOKUP("Ben", 'emp data'!B2:B, 'emp data'!A2:A), salary!A2:A, salary!C2:C)

Here is how this nested XLOOKUP formula works:

Search_key (Name): Ben.

The inner XLOOKUP formula returns the ID of “Ben.”

=XLOOKUP("Ben", 'emp data'!B2:B, 'emp data'!A2:A)

The outer XLOOKUP formula uses the ID returned by the first XLOOKUP formula as the search_key to look up Ben’s salary in the “salary” table.

Conclusion

I have shared three unique examples of nested XLOOKUP in Google Sheets. We can replace all of them with formulas using some other functions. For example, in the last example, we can use the following FILTER combination instead.

=FILTER(C2:C,A2:A=SORTN(FILTER('emp data'!A2:A,'emp data'!B2:B="Ben")))

Which one are you using, nested XLOOKUP or alternative formulas?