Nested XLOOKUP Examples for Excel

A nested XLOOKUP places one XLOOKUP inside another, so one search can supply the lookup value, return array, or fallback for the next. For a two-way lookup, with names in A2:A5, quarter headings in B1:E1, the data grid in B2:E5, the employee in H2, and the quarter in H3, use =XLOOKUP(H2,A2:A5,XLOOKUP(H3,B1:E1,B2:E5)); if H2 is Ben and H3 is Q3, the result is 13.

This pattern is useful when a task requires two related searches rather than one. The examples below show how to select a row and column, search a backup list, and chain a lookup from a descriptive label to a value. Each formula uses Excel’s default exact-match behavior unless noted.

What nested XLOOKUP means

XLOOKUP searches one range and returns a corresponding value from another. Nesting means using a complete XLOOKUP formula as an argument inside a second XLOOKUP. Excel evaluates the inner function to produce a value or array, then uses that result in the outer function.

The standard function has the form XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The square brackets indicate optional arguments; do not type them into a formula. In ordinary use, a lookup searches for an exact match by default. If it finds one, XLOOKUP returns the item in the corresponding position of the return array.

A nested formula can be harder to read than a single lookup, so nesting is most useful when the inner result genuinely determines what the outer search should do. It is not automatically faster or better, and it does not remove the need for compatible ranges and clean lookup values.

Example 1: Find a value at a row-and-column intersection

This is often called a two-way lookup. The outer XLOOKUP finds the requested row, while the inner XLOOKUP selects the column of values for the requested heading. In the opening example, the inner search finds Q3 in B1:E1 and returns the corresponding column from B2:E5. The outer search then finds Ben in A2:A5 within that column, returning 13.

For example, if the grid contains Ana’s quarterly values 12, 15, 14, and 18; Ben’s 9, 11, 13, and 12; Chen’s 16, 14, 17, and 19; and Dia’s 7, 10, 8, and 11, Ben’s Q3 value is 13. The formula works because the inner result is a vertical array with one value for each employee, matching the size and order of A2:A5.

Use the row labels as the outer lookup array.
Use the column headings as the inner lookup array.
Make the inner return array cover the complete data grid, not just one row.

If the table grows, update the ranges or use an Excel Table so references can expand with the data. The selected heading must match a heading in the table; for example, Q3 and Qtr 3 are not the same text. For a table where labels are in the first column and headings are in the first row, this nested pattern is generally more direct than building a separate formula for every row-column combination.

Example 2: Search a backup list when the main list has no match

A nested XLOOKUP can act as a fallback. Put the backup search in the outer function’s if_not_found argument. Excel uses that result if the first search cannot find the requested ID. With the current IDs in A2:A5 and their names in B2:B5, archive IDs in D2:D4 and archive names in E2:E4, and the requested ID in G2, use the following formula:

Suppose G2 contains C104, that ID is absent from A2:A5, and the archive contains C104 beside Maya. The formula returns Maya. If neither list contains the ID, it returns Not found.

=XLOOKUP(G2,A2:A5,B2:B5,XLOOKUP(G2,D2:D4,E2:E4,"Not found"))

The source ranges need not have the same number of rows, but each lookup array must correspond position by position to its own return array. This matters when a current register and an archive have different lengths. Keep their IDs and names paired within each source rather than combining mismatched ranges.

For several sources, the fallback can be extended by placing another XLOOKUP in the second lookup’s if_not_found argument. As these formulas grow, however, source order and error messages become harder to inspect. For a long list of sources, a consolidated table or a helper column can be easier to maintain.

Example 3: Look up a product name, then find its price

Sometimes a user knows a product name, but the price table is indexed by SKU. The inner search can translate the name into a SKU, and the outer search can use that SKU to retrieve the price. With product names in A2:A5, their SKUs in B2:B5, SKUs in D2:D5, prices in E2:E5, and the requested product name in H2, use:

=XLOOKUP(XLOOKUP(H2,A2:A5,B2:B5,"No product"),D2:D5,E2:E5,"No price")

If H2 contains Desk Lamp, the product-name list returns SKU L-204, and the price list has L-204 beside 34.99, the formula returns 34.99. Format the result cell as currency if you want a currency symbol; the lookup itself returns the stored number and does not choose a display format.

There is a useful error-message detail here: if the product name is missing, the inner function returns No product, and the outer function then searches for that text as a SKU. Since it is unlikely to find it, the final result will usually be No price. If you need to distinguish those two cases precisely, use a helper cell for the SKU or design a separate check for the name before performing the price lookup.

Example 4: Let one lookup choose the return column

The two-way lookup is one version of a broader idea: an inner search can produce the return array for an outer search. This is handy when the column to return changes according to a selected heading. In the quarterly example, the inner function returns a full column of the grid, not a single cell. The outer function then picks the one employee’s entry from that column.

Check the range orientation carefully. The heading lookup B1:E1 is horizontal, and its return array B2:E5 is a rectangular block with four columns. XLOOKUP uses the matching heading’s position to return the corresponding column from that block. The outer lookup array A2:A5 has four entries, and the selected column also has four, so the row positions align.

A frequent mistake is to give the inner function a return range with only one row, such as B2:E2, when the outer function is meant to search all employees. That yields only one value, not a complete column of employee results. Another mistake is to include a header in one range but not the other, shifting the correspondence between labels and values.

How to build and check a nested formula

Building the inner lookup first makes nested formulas easier to diagnose. Confirm that it returns the value or array required by the outer search before combining the two parts.

Write down the requested result and identify the final search: what should the outer XLOOKUP find, and what should it return?
Identify the intermediate result the outer search needs, such as a SKU, a selected data column, or a backup result.
Build the inner XLOOKUP by itself and verify its lookup and return ranges line up.
Replace the outer function’s relevant argument with the working inner formula, then test a known match and a missing value.
Add an if_not_found message only after the match behavior is correct. Use clear text so a missing record is not confused with a valid blank.

When troubleshooting, temporarily enter the inner function in a spare cell. If it returns an unexpected value, the issue is inside that lookup; if it returns the expected value, inspect the outer ranges and arguments. In Microsoft 365 for Windows, XLOOKUP is available directly in worksheet formulas. It is also available in Excel for Mac and Excel for the web when supported by the user’s Excel version. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016. A workbook using it may show a _xlfn error in older versions.

Common problems and misconceptions

Wrong range size or orientation: A lookup array and its corresponding return array must align. For the two-way pattern, the inner return block must include the rows the outer lookup searches.
Unexpected not-found text: In a chain, a not-found result from the inner function becomes an input to the outer function. Choose messages with that behavior in mind, or use a helper cell to show each stage.
Numbers stored as text: An ID that looks like 104 in one range may be text while its counterpart is numeric. They may not match as expected. Normalize the source data rather than adding increasingly complex conversions to a formula.
Duplicate lookup values: XLOOKUP returns the first matching item by default, searching from first to last. If duplicates exist, confirm that the first match is the intended record or use a deliberate search mode where appropriate.
Assuming nesting changes match rules: Nesting does not make a search approximate. XLOOKUP defaults to exact match; an optional match mode can change behavior, but should be added only when the data calls for it.

Also remember that an empty-looking result might be a genuinely blank return cell rather than a failed lookup. A custom if_not_found message distinguishes a missing key from a match whose associated cell is blank, though a helper formula may be needed if the distinction has to be reported in a particular way.

Nested XLOOKUP compared with alternatives

For a two-way lookup, nested XLOOKUP is often easier to follow than a combination of INDEX and MATCH because each search reads naturally: find the heading, then find the row in the selected values. INDEX and MATCH remain useful for workbooks that must support older Excel versions without XLOOKUP. They can also suit teams already using established formulas built around those functions.

Helper cells are a practical alternative when a nested formula becomes difficult to audit. One cell can display the intermediate SKU or selected column result, and a second can return the final value. This uses more worksheet space but makes each stage visible. For repeated lookups across a large dataset, consider whether a well-structured table, a unique key, or a data-cleaning step can simplify the model before adding more layers of formulas.

Frequently Asked Questions

Can XLOOKUP be nested inside another XLOOKUP?
Yes. A complete XLOOKUP can be used as an argument to another XLOOKUP, including as a lookup value, a return array, or an if_not_found result. The inner function runs to supply the outer function with what it needs.

How do I do a two-way lookup with XLOOKUP?
Use an inner XLOOKUP to select a column based on the desired heading, then an outer XLOOKUP to find the requested row in that column. Make sure the selected column has the same number and order of entries as the outer lookup array.

Can nested XLOOKUP search more than one table?
Yes. Put a second search in the first search’s if_not_found argument to search a backup table. You can extend the pattern to further sources, but a combined table or helper cells may be easier to maintain when there are many.

Why does my nested XLOOKUP return the wrong result?
Check that lookup values have the same data type, that each lookup and return range is aligned, and that duplicate keys are not causing the first match to be returned. Test the inner function separately to locate which stage is producing the unexpected result.

Does XLOOKUP work in older Excel versions?
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported Mac and web editions. It is not available as a native function in Excel 2019 or Excel 2016; use a compatible alternative such as INDEX and MATCH if the workbook must run there.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.