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)Excel and Google Sheets use the same formula syntax for this pattern.
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 |
Formula explanation
- 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.
What this formula does
XLOOKUP Left Lookup Formula is built around XLOOKUP. The example formula on this page is =XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0), and it is written against sample columns such as SKU, Item, Category, Price, Stock.
Excel and Google Sheets use the same copy pattern here, so the main work is adjusting ranges, criteria, and output cells to match your sheet.
For lookup work, the lookup key must identify the intended row before the return range is read. Check the key column, return column, and match mode before copying the formula into a production workbook.
Use this pattern for this task: Look up a value to the left with XLOOKUP. If duplicate keys exist, decide whether the first matching row is acceptable or whether you need a multiple-criteria lookup instead.
| Syntax piece | Role in the formula |
|---|---|
| D2:D100 | D2:D100 is the lookup array. |
| A2:A100 | A2:A100 is the return array even though it sits to the left. |
| XLOOKUP | This is a main reason to use XLOOKUP instead of VLOOKUP. |
Practical use cases
=XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0)Excel: Use this in a product, price, or customer reference table where SKU identifies the row and Stock is the value you need to return. Returns: If F2 is 49, the formula returns A-100 from the SKU column.
=XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0)Google Sheets: Use this in a workbook where an input cell drives a report and the formula must return one aligned value without manually filtering the source table. Returns: If F2 is 49, the formula returns A-100 from the SKU column.
Common errors
- Lookup and return ranges must line up row by row.
- Use exact match for IDs, SKUs, emails, and names unless the lookup table is intentionally sorted for approximate match.
- Blank or duplicate lookup keys can make a correct formula return an unexpected row.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | Lookup and return ranges must line up row by row. | Confirm the lookup value exists, use exact match for IDs, and make sure the lookup and return ranges line up row by row. |
| Common setup problem | Use exact match for IDs, SKUs, emails, and names unless the lookup table is intentionally sorted for approximate match. | Confirm the lookup value exists, use exact match for IDs, and make sure the lookup and return ranges line up row by row. |
| Common setup problem | Blank or duplicate lookup keys can make a correct formula return an unexpected row. | Confirm the lookup value exists, use exact match for IDs, and make sure the lookup and return ranges line up row by row. |
| #N/A or not-found result | The lookup key is missing, has a hidden space, or does not match the key column's data type. | Trim the key values, verify the lookup range, and use an explicit fallback message when the formula supports one. |
| Wrong matching row | The lookup table contains duplicate keys or approximate match is being used on unsorted data. | Switch to exact match for IDs, or use a multiple-criteria lookup when one key is not unique enough. |
When not to use this formula
- Do not use a lookup formula to summarize many rows; use SUMIFS, COUNTIFS, FILTER, or QUERY instead.
- Do not use approximate lookup for unsorted product, customer, or task lists.
Alternatives
| Alternative | When to use it |
|---|---|
| XLOOKUP Formula Builder | Best modern lookup builder with not-found handling. |
| INDEX MATCH Formula Builder | Use when you need broad compatibility or left lookups. |
| VLOOKUP Formula Builder | Use for older workbooks where the lookup column is first. |
Compare with related formulas
| Formula or tool | How it differs | Use it instead when |
|---|---|---|
| XLOOKUP Formula Builder | XLOOKUP Formula Builder solves a nearby workflow, while XLOOKUP Left Lookup Formula is focused on look up a value to the left with xlookup. | Best modern lookup builder with not-found handling. |
| INDEX MATCH Formula Builder | INDEX MATCH Formula Builder solves a nearby workflow, while XLOOKUP Left Lookup Formula is focused on look up a value to the left with xlookup. | Use when you need broad compatibility or left lookups. |
| VLOOKUP Formula Builder | VLOOKUP Formula Builder solves a nearby workflow, while XLOOKUP Left Lookup Formula is focused on look up a value to the left with xlookup. | Use for older workbooks where the lookup column is first. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Excel copy output | Excel | =XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0) | If F2 is 49, the formula returns A-100 from the SKU column. |
| Google Sheets copy output | Google Sheets | =XLOOKUP(F2, D2:D100, A2:A100, "Not found", 0) | If F2 is 49, the formula returns A-100 from the SKU column. |
Related formulas
FAQ
What does the XLOOKUP Left Lookup Formula do?
It supports this task: Look up a value to the left with XLOOKUP. The example uses F2, but you should replace the ranges and criteria with the cells in your own workbook.
Does the XLOOKUP Left Lookup Formula work in Excel and Google Sheets?
Yes. The Excel and Google Sheets versions on this page use the same syntax for this pattern.
What should I change before copying the formula?
Change the source ranges, criteria cells, and output range references so they match your sheet. Keep related ranges on the same rows unless the formula notes say otherwise.
Why does my lookup formula return #N/A?
The key may not exist, may contain a hidden space, or may be stored as text while the lookup table stores numbers. Check the key column before changing the formula.
Can this lookup return more than one matching row?
Lookup formulas normally return one aligned result. Use FILTER or QUERY if you need all matching rows.
Should I use exact match or approximate match?
Use exact match for IDs, names, SKUs, emails, and unsorted lists. Approximate match is only safe for sorted breakpoints such as tiers or rates.