# Why does XLOOKUP show #NAME? and what can I use instead?

Your Excel predates XLOOKUP, which arrived in Excel 2021 and Microsoft 365. Use INDEX and MATCH: =INDEX(C2:C9,MATCH(G1,A2:A9,0)).

**Excel:** `=INDEX(C2:C9,MATCH(G1,A2:A9,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price | Stock |  | SKU | P-104 |
| P-101 | Desk lamp | 39.99 | 140 |  | Price |  |
| P-102 | Office chair | 249 | 32 |  |  |  |
| P-103 | Monitor arm | 89.5 | 75 |  |  |  |
| P-104 | Standing desk | 449 | 18 |  |  |  |
| P-105 | Keyboard | 74.95 | 210 |  |  |  |
| P-106 | Webcam | 59 | 96 |  |  |  |
| P-107 | Headset | 129 | 54 |  |  |  |
| P-108 | Laptop stand | 45 | 160 |  |  |  |

Formula in G2: `=INDEX(C2:C9,MATCH(G1,A2:A9,0))`

Result: $449.00

## How it works

Excel 2019 and earlier don't know XLOOKUP, so they treat the name as unknown and return #NAME?. INDEX and MATCH do the same job in every version: MATCH finds the position of the SKU, and INDEX returns the price from that position.

## Other ways

- `=VLOOKUP(G1,A2:D9,3,FALSE)`: Also works in every version when the lookup column is the table's first column.
- `=XLOOKUP(G1,A2:A9,C2:C9)`: The XLOOKUP that shows #NAME? in Excel 2019 and earlier. It works in Excel 2021, Microsoft 365 and Google Sheets.

## Common mistakes

- A file made in Microsoft 365 and opened in Excel 2019 shows the formula as `_xlfn.XLOOKUP` with #NAME?. FILTER, UNIQUE, SORT and LET do the same.
- Don't leave out MATCH's 0, or you get an approximate match.

## Questions

**Which versions have XLOOKUP?**

Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Google Sheets has it too.

**How do I check my Excel version?**

File, Account, About Excel.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/xlookup-name-error-in-older-excel