Lookup formula page
XLOOKUP Left Lookup Formula
Use XLOOKUP for left lookups where VLOOKUP cannot return a column before the lookup column.
Look up a value to the left with XLOOKUP.
If F2 is 49, the formula returns A-100 from the SKU column.
Copy formulas
=XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0)=XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0)XLOOKUP can return from a column to the left because its lookup and return arrays are separate. Excel and Google Sheets use the same exact-match structure here.
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 49, the formula returns A-100 from the SKU column.
How the formula works
- D2:D100 is the lookup array.
- A2:A100 is the return array even though it sits to the left.
- This is a main reason to use XLOOKUP instead of VLOOKUP.
| Syntax piece | Role in the formula |
|---|---|
| F2 | The price or other value to find. |
| D2:D100 | The price lookup array. |
| A2:A100 | The SKU return array, positioned to the left of the lookup array. |
| "Not found" | The no-match fallback. |
| 0 | The exact-match mode. |
Verified examples
=XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0)Excel: Enter 49 in F2 and use the sample product table. Returns: A-100
=XLOOKUP(F2,D2:D100,A2:A100,"Not found",0)Google Sheets: Enter 7 in F2. Returns: B-201
=XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0)Excel: Enter 999 in F2; it is absent from the sample table. Returns: Not found
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A numeric price returns Not found | One price is stored as text while the other is numeric. | Convert both values to the same type before looking up, and check for currency symbols or spaces. |
| The returned SKU is from the wrong row | The lookup and return arrays are not aligned to the same rows. | Use matching row boundaries for D2:D100 and A2:A100. |
| Duplicate prices return one SKU | XLOOKUP returns the first matching price by default. | Use a unique lookup key or FILTER if all SKUs at that price are needed. |
When not to use this formula
- Do not use a price as the lookup key when prices are not unique and a single deterministic SKU is required.
Alternatives
| Alternative | When to use it |
|---|---|
| INDEX MATCH Left Lookup Formula | Use when XLOOKUP is unavailable and separate ranges are still needed. |
| XLOOKUP Formula Builder | Use when the lookup and return ranges need to be configured interactively. |
Related formulas
Official references
- XLOOKUP function from Microsoft
FAQ
Can XLOOKUP return a value to the left?
Yes. The return array is independent of the lookup array, so it can be on the left.
What happens when two products have the same price?
The default XLOOKUP behavior returns the first matching row. Use FILTER for every matching SKU.