On this page · 11 sections
- XLOOKUP syntax and arguments
- Sample data: a staff directory and commission tiers
- How to use XLOOKUP, step by step
- Eight XLOOKUP examples with exact formulas
- XLOOKUP with multiple criteria
- XLOOKUP vs VLOOKUP
- XLOOKUP not available? Versions and fixes
- Common XLOOKUP errors
- For IT teams and businesses
- How eCorpIT can help
- References
Summary. To use XLOOKUP, give it the value to find, the range to search and the range to return: =XLOOKUP(1003,A2:A6,B2:B6) finds employee ID 1003 in A2:A6 and returns the name from the same row of B2:B6. It has three required and three optional arguments (if_not_found, match_mode and search_mode) and matches exactly by default. Unlike VLOOKUP, it can look left, needs no column number and can return several columns at once. Match mode -1 returns the next smaller item, 1 the next larger and 2 enables wildcards; search mode -1 returns the last match. XLOOKUP works in Excel for Microsoft 365 on Windows and Mac, Excel 2024, Excel 2021, Excel for the web and the mobile apps. It is not available in Excel 2016 or 2019, which show #NAME? and lost support on 14 October 2025; use INDEX with MATCH, VLOOKUP or Excel for the web there.
About this article. For anyone who builds Excel workbooks. Checked on 15 November 2026 against Microsoft Support function and error pages, Microsoft Learn update-history and lifecycle pages, and Excel team blog posts. The steps apply to Excel for Microsoft 365 (Current Channel Version 2610, Build 20527.20196) and Excel for Mac 16.115, both released on 10 November 2026, plus Excel 2024, Excel 2021 and Excel for the web. Labels can differ slightly by version; no vendor paid for inclusion. Researched with AI assistance and reviewed by our editorial team.
XLOOKUP syntax and arguments
Microsoft documents the syntax as follows.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Required | What it does |
|---|---|---|
| lookup_value | Yes | The value to search for |
| lookup_array | Yes | The range or array to search |
| return_array | Yes | The range or array to return results from |
| if_not_found | No | Text or value to show when nothing matches; without it you get #N/A |
| match_mode | No | 0 exact (default); -1 exact or next smaller; 1 exact or next larger; 2 wildcard match |
| search_mode | No | 1 first to last (default); -1 last to first; 2 binary search on ascending data; -2 binary search on descending data |
Leave an optional argument empty by typing two commas: =XLOOKUP(A1,B:B,C:C,,-1) skips if_not_found and sets match_mode to -1.
A regular-expression match mode (3) reached Beta Channel Insiders as a preview in August 2024, but the help page still lists only the four modes above.
Sample data: a staff directory and commission tiers
Type this into a blank sheet from A1, with the tier table in G1:H5.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Office | Extension |
| 2 | 1001 | Ava Patel | Finance | London | 4410 |
| 3 | 1002 | Ben Carter | Sales | New York | 2215 |
| 4 | 1003 | Chloe Evans | IT | Manchester | 4472 |
| 5 | 1004 | Diego Ramos | Sales | Chicago | 2290 |
| 6 | 1005 | Emma Hughes | IT | New York | 2238 |
| Row | G | H |
|---|---|---|
| 1 | Sales from | Rate |
| 2 | 0 | 2% |
| 3 | 10,000 | 4% |
| 4 | 25,000 | 6% |
| 5 | 50,000 | 8% |
How to use XLOOKUP, step by step
The steps are the same on Windows, Mac and the web.
- Select an empty cell, for example J1.
- Type
=XLOOKUP(. Once you type a function name and the opening bracket, Excel shows its syntax as a tooltip.
- Type 1003, then a comma.
- Select A2:A6 (the column to search), then type a comma.
- Select B2:B6 (the column to return).
- Type a closing bracket and press Enter.
=XLOOKUP(1003,A2:A6,B2:B6)
The result is Chloe Evans, with no column counting and no FALSE: XLOOKUP pairs the two ranges row by row and matches exactly by default. To copy the formula down, lock the ranges, as in $A$2:$A$6: select each reference in the formula bar and press F4 on Windows, or Cmd+T or F4 on a Mac.
Eight XLOOKUP examples with exact formulas
Results, after each arrow, come from the sample data.
Exact match: =XLOOKUP(1003,A2:A6,B2:B6) -> Chloe Evans
Left lookup: =XLOOKUP("Diego Ramos",B2:B6,A2:A6) -> 1004
Not found: =XLOOKUP(1009,A2:A6,B2:B6,"Not found") -> Not found
Three columns: =XLOOKUP(1002,A2:A6,B2:D6) -> Ben Carter | Sales | New York
Next smaller: =XLOOKUP(32000,G2:G5,H2:H5,,-1) -> 6%
Next larger: =XLOOKUP(32000,G2:G5,H2:H5,,1) -> 8%
Wildcard: =XLOOKUP("Em*",B2:B6,C2:C6,,2) -> IT
Last match: =XLOOKUP("Sales",C2:C6,B2:B6,,0,-1) -> Diego Ramos
A few notes on these:
- Left lookup. The lookup column (B) is to the right of the return column (A), which VLOOKUP cannot handle.
- Several columns. With return_array B2:D6 the result is three cells wide and spills to the right; keep those cells empty or you get #SPILL!.
- Tiers. 32,000 is not in G2:G5, so match_mode -1 falls back to 25,000 (6%) and match_mode 1 moves up to 50,000 (8%). Microsoft attaches a sorting requirement only to the binary search modes, so these two modes do not need a sorted tier list.
- Wildcards. XLOOKUP treats
*,?and~as special only when match_mode is 2. VLOOKUP applies wildcards whenever range_lookup is FALSE and the lookup value is text.
- First or last. Without search_mode, the Sales lookup returns Ben Carter, the first Sales row; search mode -1 starts from the bottom and returns Diego Ramos.
Binary search modes (2 and -2) suit large sorted lists. Microsoft warns that they return invalid results if the data is not sorted in the stated order, so keep the default search mode unless you have a reason not to.
XLOOKUP with multiple criteria
XLOOKUP has no built-in second criterion, but two patterns work.
Boolean logic. To find the extension for someone in IT based in New York:
=XLOOKUP(1,(C2:C6="IT")*(D2:D6="New York"),E2:E6)
Each comparison returns TRUE or FALSE per row, and multiplying them gives 1 only where both are TRUE, so XLOOKUP finds that 1 and returns 2238 (Emma Hughes). Microsoft uses the same multiplication for AND conditions in FILTER. Add conditions by multiplying more brackets. With criteria in input cells such as J10 and J11, use those cells instead of the text, and a drop-down list on each stops typing errors.
Joined keys. Combine the criteria into one text string on both sides:
=XLOOKUP("IT|New York",C2:C6&"|"&D2:D6,E2:E6)
This also returns 2238; the | separator stops accidental matches when values run together. Both patterns return the first row that meets every condition. For every matching row, use FILTER instead.
XLOOKUP vs VLOOKUP
Microsoft's XLOOKUP page sums up the main difference: a lookup array and a return array instead of one table array and a column index number. The Excel team's August 2019 launch post added VLOOKUP's other limits: approximate match by default, no lookups to the left, no search from the bottom, and column numbers that need editing when columns are inserted or deleted. The table draws on both help pages.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match | Approximate (TRUE) unless you add FALSE | Exact |
| Return column | Counted by number (col_index_num) | A separate range |
| Look to the left | No | Yes |
| Not-found handling | Wrap in IFNA or IFERROR | Built in (if_not_found) |
| Last match | No | Yes, search_mode -1 |
| Wildcards | Automatic with FALSE and text | Only with match_mode 2 |
| Several columns | One per formula | Yes, spills |
| Excel 2016 and 2019 | Yes | No (#NAME?) |
The same lookup both ways:
=VLOOKUP(1003,$A$2:$E$6,2,FALSE)
=XLOOKUP(1003,$A$2:$A$6,$B$2:$B$6)
For the older function in full, see our VLOOKUP guide. XLOOKUP also replaces HLOOKUP: point it at a header row and a result row and it searches across. Microsoft's HLOOKUP page itself recommends XLOOKUP, and our HLOOKUP guide covers that case.
XLOOKUP not available? Versions and fixes
Microsoft's XLOOKUP page lists Excel for Microsoft 365 on Windows and Mac, Excel 2024 and Excel 2021 (Windows and Mac), and Excel for iPad, iPhone and Android. Excel for the web is not on that list, but Microsoft's alphabetical function list, which covers the web version, marks XLOOKUP as an Excel 2021 function and does not flag it as unavailable there, as it does WEBSERVICE.
It is not available in Excel 2016 or Excel 2019. When those versions open a workbook that uses it, the function appears with an _xlfn. prefix and the formula shows #NAME? once it calculates. Your options:
- Check your version. On Windows, select File > Account and look under Product Information; on a Mac, open the Excel menu and select About Excel. Functions marked with a version are not available in earlier ones, and Excel 2016 and 2019 left support on 14 October 2025, so no update will add XLOOKUP.
- Open the file in Excel for the web. Microsoft offers Excel for the web free with a Microsoft account.
- Rewrite the formula. Replace
=XLOOKUP(1003,A2:A6,B2:B6)with=INDEX(B2:B6,MATCH(1003,A2:A6,0))or an exact-match VLOOKUP.
- On Microsoft 365 and still seeing #NAME? Check the spelling first, the most common cause, then install the latest updates or try Excel for the web.
Across workbooks. Microsoft notes that dynamic array formulas linked between workbooks need both workbooks open; with the source closed, they return #REF! on refresh. For multi-column lookups from another file, keep the source open or copy the data in.
Common XLOOKUP errors
- #N/A. No match and no if_not_found value. As with VLOOKUP, the usual causes are numbers stored as text, stray spaces or a typo; Microsoft's #N/A guide covers each.
- #VALUE! Make lookup_array and return_array the same size, such as A2:A6 with B2:B6 rather than B2:B7. A volunteer moderator on Microsoft Q&A traced one #VALUE! case to exactly this mismatch; Microsoft's XLOOKUP page does not list error causes.
- #SPILL! A multi-column result has no room to spill. Select the error cell to see the intended spill range, then clear the cells in the way.
- #NAME? A misspelt function name, text without quotation marks, or a version without XLOOKUP.
For IT teams and businesses
XLOOKUP is a sensible default for new workbooks in a Microsoft 365 organisation, but mixed versions are the trap. Anyone on Excel 2016 or 2019 sees #NAME? on every XLOOKUP, and those versions get no more security updates. Excel 2021 runs XLOOKUP, but its support ended on 13 October 2026. Before standardising, confirm which builds are in use, including at partners who receive your files, and keep a VLOOKUP or INDEX and MATCH version of templates that leave the organisation. If the lookups feed a recurring report, a data model may suit it better; our Power BI consulting guide covers the licence choices.
How eCorpIT can help
eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team helps organisations clean up spreadsheet lookup logic and move recurring Excel reports into maintained data models. Talk to us at /contact-us/.
Last updated: 15 November 2026.
