XLOOKUP advanced examples include returning several columns at once, matching on multiple conditions, finding the last occurrence, and using approximate or wildcard matches. In Microsoft 365 for Windows, with IDs in A2:A100, amounts in C2:C100, and the ID to find in E2, use =XLOOKUP(E2,A2:A100,C2:C100,"Not found",0); it returns the matching amount or “Not found” if there is no exact match.
How XLOOKUP works
XLOOKUP searches one range and returns a corresponding value from another. Its basic arguments are the value to find, the lookup array, the return array, an optional result for a missing match, a match mode, and a search mode. Lookup and return arrays can be vertical columns or horizontal rows, but they need compatible dimensions. Unlike older VLOOKUP formulas, XLOOKUP does not need a column number, and its return range can sit to either side of the lookup range.
Exact matching is the default, so the final 0 in the opening example is optional. Writing it explicitly can make a complicated formula easier to review. The optional “Not found” text handles a missing match without an IFERROR wrapper. It does not suppress every possible error: for example, an error already present in the lookup data can still affect a result.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Its formula syntax is the same in Excel for Mac and Excel for the web, though ribbon placement and some interface details can differ. Older Excel editions may display a function-name error when they open a workbook containing XLOOKUP.
Return multiple columns with one lookup
A lookup does not have to return just one cell. If a matched record has several useful fields, XLOOKUP can return a multi-column range. With product IDs in A2:A100, product names, categories, and prices in B2:D100, and the requested ID in G2, enter:
=XLOOKUP(G2,A2:A100,B2:D100,"Not found")
If G2 contains an ID whose row has “Desk lamp,” “Lighting,” and 34.99 in columns B through D, the formula returns those three values across adjacent cells. In current Microsoft 365, the result spills into the cells to the right. Keep that spill area clear; otherwise Excel returns a #SPILL! error. A formula entered in a conventional Excel Table calculated column cannot spill in the same way, so place it in a normal worksheet range when you want the returned fields to spread across cells.
This pattern is useful for a compact lookup panel: one input cell can populate a group of details without repeating the lookup formula for each field. It also makes changing the returned fields easier: adjust the return range rather than editing a column index.
Match on multiple conditions
For a two-condition lookup, multiply Boolean tests to create an array of 1s and 0s. With department names in A2:A100, months in B2:B100, sales in C2:C100, the desired department in G2, and the desired month in H2, use:
=XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:C100,"No match")
Each comparison produces TRUE for a matching row and FALSE for a nonmatching row. Multiplying the results treats TRUE as 1 and FALSE as 0; only a row meeting both conditions produces 1. XLOOKUP then returns the corresponding sales value. For example, if the first row matching “West” and “March” has sales of 12,500, the formula returns 12,500.
To add a third condition, multiply in another comparison, such as a status range compared with a cell containing “Approved.” Make sure every compared range and the return range cover the same rows. If several records meet all conditions, the formula returns the first match in the search direction; use a reverse search mode if the latest matching record is wanted.
Build a two-way lookup
A two-way lookup selects a row and a column, such as finding a particular employee’s value for a chosen month. Suppose A1:D1 contains headers ID, East, West, and Central; A2:A100 contains IDs; and B2:D100 contains regional figures. Put a region header such as “West” in H2 and an ID such as 1042 in H3. Use:
=XLOOKUP(H2,A1:D1,XLOOKUP(H3,A2:A100,A2:D100,"Not found"),"Header not found")
The inner XLOOKUP finds the record for ID 1042 and returns its whole row across A:D. The outer XLOOKUP uses the chosen header to pick the corresponding value from that row. If the matching record has 8,400 under West, the result is 8,400. The header row and returned row must represent the same fields in the same order. For a single returned field, this nested structure is more flexible than hard-coding a column number.
Use approximate and wildcard matches
Match mode changes how XLOOKUP treats the lookup value. The default, 0, requests an exact match. Use -1 for an exact match or the next smaller item, and 1 for an exact match or the next larger item. These modes are useful for tier tables, such as commission rates, score bands, or shipping thresholds.
For example, suppose A2:A6 contains ascending lower bounds 0, 60, 70, 80, and 90, while B2:B6 contains the corresponding grade labels F, D, C, B, and A. With a score of 73 in E2, use:
=XLOOKUP(E2,A2:A6,B2:B6,"No band",-1)
The next smaller threshold is 70, so the formula returns C. The thresholds should be arranged in ascending order for this example, and the meaning of “next smaller” should match the way the table defines its bands. Check boundary values such as 60 and 90 to confirm the chosen labels are right for your policy.
Match mode 2 enables wildcard matching. With search text in E2, descriptions in A2:A20, and corresponding codes in B2:B20, the following searches for the text anywhere within a description:
=XLOOKUP("*"&E2&"*",A2:A20,B2:B20,"No match",2)
If E2 contains “cable,” a description such as “USB cable, 2 m” can match. The asterisk represents any number of characters; a question mark represents one character. Wildcard matching is not case-sensitive. If the value in E2 itself contains wildcard characters that should be treated literally, they need to be escaped with a tilde.
Find the first or last matching record
By default, XLOOKUP searches from the first item toward the last. Its search-mode argument can reverse that direction. With transaction IDs in A2:A100, dates in B2:B100, and the ID to locate in E2, this returns the date on the last matching row:
=XLOOKUP(E2,A2:A100,B2:B100,"Not found",0,-1)
This is useful when a log contains repeated IDs and new entries are appended at the bottom. It returns the last occurrence in the range, not necessarily the latest calendar date. Those are different ideas if rows have been sorted or imported out of order. To get the chronologically latest date, ensure the data or the lookup logic reflects that requirement.
The same direction setting can help resolve duplicates deliberately. Before relying on it, decide whether the first or last record is authoritative and whether the range includes all relevant rows. XLOOKUP does not warn that a key appears more than once; it simply returns the first or last match according to the selected search mode.
Search a primary list and then a backup
The if-not-found argument can contain another XLOOKUP. This allows a search in a current list first and a legacy list second. Suppose the requested ID is in E2, current IDs and results are in A2:A20 and B2:B20, and legacy IDs and results are in D2:D20 and F2:F20. Use:
=XLOOKUP(E2,A2:A20,B2:B20,XLOOKUP(E2,D2:D20,F2:F20,"Not found"))
When the ID exists in the current list, the formula returns its B-column result. If it does not, Excel evaluates the backup lookup and returns the corresponding F-column result. If neither list contains the ID, the result is “Not found.” This pattern is useful during a transition between tables, but it can conceal duplicate or conflicting records. If an ID should be unique across both lists, consider checking and resolving duplicates rather than accepting whichever list takes precedence.
Use binary search only with sorted data
XLOOKUP also offers binary search modes for large sorted ranges: 2 searches an ascending lookup array, while -2 searches a descending one. With ascending numeric IDs in A2:A100000, associated values in B2:B100000, and the exact ID in E2, use:
=XLOOKUP(E2,A2:A100000,B2:B100000,"Not found",0,2)
Binary search can be faster on very large ranges, but it depends on the lookup array being correctly sorted in the direction specified. If that condition is not met, results may be incorrect; Excel does not automatically sort the data or reliably flag an ordering problem. For typical-sized lists, the default linear search is usually simpler and safer. Use binary mode only when the sort order is controlled and maintained.
Common problems and practical checks
A #N/A result usually means there is no match under the selected match mode, or that visually similar values differ. Common causes include a number stored as text, leading or trailing spaces, inconsistent punctuation, and mismatched date values. The optional if-not-found argument is a clear way to label a genuine missing match, but it should not be used to hide a data-quality issue that needs fixing.
- Check that lookup and return ranges have matching lengths and align row by row.
- Check whether duplicate keys make the first-versus-last choice important.
- Leave room for spill results when returning multiple columns.
- For approximate matches, review the threshold order and test boundary values.
- For binary search, verify the sort order before choosing search mode 2 or -2.
When a workbook will be shared with people using Excel 2019 or earlier, compatibility matters: those editions do not support XLOOKUP. A formula using it may not calculate there. If recipients cannot use a supported Excel version, choose a compatible alternative or provide a separate version of the workbook.
Frequently Asked Questions
Can XLOOKUP return more than one result?
Yes. Set the return array to multiple adjacent columns, such as B2:D100. In Microsoft 365, the matched row can spill into neighboring cells, provided the spill area is empty.
How do I make XLOOKUP return the last match?
Set the search-mode argument to -1. For an exact match, use match mode 0 as well, as in =XLOOKUP(E2,A2:A100,B2:B100,"Not found",0,-1). The formula returns the last matching row in the specified range.
Is XLOOKUP case-sensitive?
No. Standard exact and wildcard matching with XLOOKUP is not case-sensitive. A lookup for “abc” can match “ABC.” A separate case-sensitive approach is needed when capitalization must distinguish values.
Why does XLOOKUP show #SPILL!?
A formula returning multiple cells needs empty space for its results. Check the cells to the right or below the formula for existing content, merged cells, or other obstructions, and clear the blocked area if appropriate.
Does XLOOKUP work in older Excel versions?
XLOOKUP is supported in Microsoft 365 and Excel 2021 or later, but not Excel 2019 and earlier. If a workbook must work in an older edition, use a compatible lookup method or confirm that every recipient can upgrade.
Related
- Heat Map Examples in PowerPoint: Ideas and How to Make Them
- Google Sheets for Data Analysis: Tools and Limits
- Google Sheets IF Formula: Examples and Common Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Link Different Sheets in Excel with FormulasTo link a cell on one Excel sheet to a cell on another, enter a reference such as…
- Excel Formula for Years of Service From Hire DateTo calculate completed years of service from a hire date in Excel, use =DATEDIF(A2,TODAY(),"Y"), assuming the hire date…
- Excel CONCAT Examples: Formulas for Joining TextTo join a first name in A2 and a surname in B2 with a space between them, enter…
- Excel RIGHT, LEFT, and MID Formulas: Syntax and ExamplesIn Excel, RIGHT extracts characters from the end of text, LEFT extracts them from the beginning, and MID…
- How to Compare Two Columns in Excel with VLOOKUP FormulaTo compare two columns in Excel, check whether each value in the first list appears in the second…
- Excel Test Cell Value: Formulas for Common ChecksTo test whether a cell contains a particular value in Excel, use an IF formula such as =IF(A2="Paid","Match","No…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.