Lookup formula page
XLOOKUP Exact Match Formula
Use XLOOKUP exact match for modern lookups where lookup and return ranges can be separate.
Find a modern exact match XLOOKUP formula.
If F2 is A-101, the formula returns 25.
Copy formulas
=XLOOKUP(F2, A2:A100, D2:D100, "Not found", 0)=XLOOKUP(F2, A2:A100, D2:D100, "Not found", 0)Excel and Google Sheets use the same XLOOKUP exact-match pattern. The separate lookup and return arrays can be different columns, while 0 requests an exact match.
Example data
| SKU | Item | Category | Price | Stock |
|---|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 | 18 |
| A-101 | Mouse | Hardware | 25 | 32 |
| B-200 | Desk Mat | Office | 18 | 9 |
| B-201 | Notebook | Office | 7 | 64 |
If F2 is A-101, the formula returns 25.
How the formula works
- F2 is the lookup value.
- A2:A100 is searched for an exact match.
- D2:D100 supplies the returned value, and Not found hides raw #N/A errors.
| Syntax piece | Role in the formula |
|---|---|
| F2 | The SKU or key to find. |
| A2:A100 | The lookup array searched for F2. |
| D2:D100 | The return array aligned to the lookup array. |
| "Not found" | The fallback shown when no exact key is found. |
| 0 | The match mode that requires an exact match. |
Verified examples
=XLOOKUP(F2, A2:A100, D2:D100, "Not found", 0)Excel: Enter A-101 in F2 and use the sample product table. Returns: 25
=XLOOKUP(F2,A2:A100,B2:B100,"Not found",0)Google Sheets: Enter B-200 in F2 and return the Item column. Returns: Desk Mat
=XLOOKUP(F2, A2:A100, D2:D100, "Not found", 0)Excel: Enter C-999 in F2; it is absent from the sample table. Returns: Not found
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A visible key returns Not found | The key and lookup values use different number or text types, or one contains hidden spaces. | Normalize both sides with TRIM or VALUE as appropriate and compare the cleaned values. |
| XLOOKUP returns a range error | The lookup and return arrays do not have the same number of rows. | Use matching boundaries such as A2:A100 and D2:D100. |
| A duplicate key returns an unexpected value | XLOOKUP returns the first matching row by default. | Remove duplicate keys or use FILTER when every matching result is required. |
When not to use this formula
- Do not use XLOOKUP when every duplicate match must be returned as a list; use FILTER instead.
Alternatives
| Alternative | When to use it |
|---|---|
| VLOOKUP Exact Match Formula | Use when the lookup key is the first column and broad legacy compatibility matters. |
| INDEX MATCH Formula | Use when XLOOKUP is unavailable but separate lookup and return ranges are needed. |
Related formulas
Official references
- XLOOKUP function from Microsoft
FAQ
What does 0 do in this XLOOKUP?
The 0 match mode requests an exact match, so a nearby key is not accepted.
Why does the fallback appear instead of #N/A?
The fourth argument is the no-match result, so XLOOKUP returns Not found when F2 is absent.