Excel formula builder
XLOOKUP Formula Builder
Generate an XLOOKUP formula for exact match, approximate match, wildcard match, or a two-condition lookup pattern. Use it when you want not-found handling and flexible return columns.
Builder inputs
Use single-criterion mode for a normal lookup, or multiple-criteria mode to match two columns at once.
=XLOOKUP(A2, Products!A2:A100, Products!D2:D100, "Not found", 0)=XLOOKUP(A2, Products!A2:A100, Products!D2:D100, "Not found", 0)Google Sheets supports XLOOKUP, but older Excel versions may require INDEX MATCH or VLOOKUP instead.How this formula works
- XLOOKUP searches the lookup array for the lookup value and returns the aligned value from the return array.
- Multiple-criteria mode searches for 1 across multiplied TRUE/FALSE criteria arrays.
- The optional not-found argument prevents raw #N/A errors in dashboards and reports.
- Match mode controls exact, approximate, and wildcard behavior.
Best fit
Best for
- Modern Excel and Google Sheets lookup formulas with clear not-found handling.
- Left lookups where the return column is before the lookup column.
- Exact-match dashboards that should show a friendly message when no match exists.
Not for
- Older Excel versions that do not support XLOOKUP. Use INDEX MATCH or VLOOKUP instead.
- Returning every matching row. Use FILTER or QUERY for row output.
- Approximate tier lookups unless your breakpoint table is sorted correctly.
Useful formula variations
=XLOOKUP(A2, Products!A2:A100, Products!D2:D100, "Not found", 0)Use this for SKUs, IDs, names, and most reference tables.
=XLOOKUP(F2, Products!B2:B100, Products!A2:A100, "Not found", 0)Looks up an item name in column B and returns the SKU from column A.
=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")Matches two conditions at once by searching for 1 across multiplied TRUE/FALSE arrays.
=XLOOKUP("*"&A2&"*", Products!B2:B100, Products!D2:D100, "Not found", 2)Use wildcard match only when partial text matching is intended.
Sample data
| SKU | Item | Category | Price |
|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 |
| A-101 | Mouse | Hardware | 25 |
| B-200 | Desk Mat | Office | 18 |
| B-201 | Notebook | Office | 7 |
Returned results from the sample data
| Lookup setup | Formula pattern | Returned result |
|---|---|---|
| A2 is A-100, lookup SKU, return Price | Exact XLOOKUP | 49 |
| F2 is Keyboard, lookup Item, return SKU | Left lookup XLOOKUP | A-100 |
| A2 is missing | XLOOKUP with if_not_found | Not found |
Match mode guide
| Match mode | Meaning | Use it for |
|---|---|---|
| 0 | Exact match | IDs, SKUs, names, emails, and most business lookups. |
| -1 | Exact or next smaller | Sorted breakpoint tables such as rates or tiers. |
| 1 | Exact or next larger | Sorted threshold tables where the next larger match is required. |
| 2 | Wildcard match | Partial text searches with * or ? characters. |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| #N/A result | The lookup value is missing, has hidden spaces, or uses a different data type from the lookup array. | Check the source key, trim spaces, and use the if_not_found argument for friendly output. |
| Multiple criteria returns not found | One criteria range does not align with the return array or one condition never matches. | Make all ranges the same height and test each condition separately before combining them. |
| Wildcard match returns the wrong row | The wildcard pattern is too broad or match mode 2 was used for data that needs exact matching. | Use exact match mode 0 unless partial text matching is the intended behavior. |
Common mistakes
- The lookup array and return array must have compatible dimensions.
- Wildcard match mode only matters when the lookup value contains wildcard characters.
- Use exact match for SKUs, IDs, names, and most business lookup keys.
- Multiple-criteria ranges must align with the return array row by row.
Related formulas
FAQ
Does XLOOKUP work in Google Sheets?
Yes. Google Sheets supports XLOOKUP, and the generated syntax on this page works for normal exact-match and multiple-criteria patterns.
Can XLOOKUP look left?
Yes. Put the left-side column in the return array and the lookup column in the lookup array.
Why does XLOOKUP return #N/A?
The lookup value was not found or the source values do not match exactly. Check hidden spaces, mixed text-number IDs, and range alignment.
When should I use XLOOKUP instead of VLOOKUP?
Use XLOOKUP when your workbook supports it and you want not-found handling, left lookup support, or separate lookup and return arrays.
Are these formulas generated with AI?
No. The builder uses built-in spreadsheet rules in your browser and does not call an AI service.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.