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

Formula (Excel and Google Sheets)

=INDEX(C2:C9,MATCH(G1,A2:A9,0))

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

Result on the example: $449.00 in G2
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.

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.

=INDEX(C2:C9,MATCH(G1,A2:A9,0))

Result449

C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …
A range: 8 cells in one column.
G1"P-104"
The value in G1.
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …
A range: 8 cells in one column.
MATCH(G1,A2:A9,0)4
MATCH: Position of a value in a list. 0 = exact match.
- lookup_valueG1"P-104"
- lookup_arrayA2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …
- match_type0
INDEX(C2:C9,MATCH(G1,A2:A9,0))449
INDEX: Value at a given row and column of a range.
- arrayC2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …
- row_numMATCH(G1,A2:A9,0)4

In Google Sheets

The same formula works in Google Sheets: =INDEX(C2:C9,MATCH(G1,A2:A9,0)). We ran it in Google Sheets mode on the same data and it returns $449.00.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=VLOOKUP(G1,A2:D9,3,FALSE)	$449.00	Also works in every version when the lookup column is the table's first column.
=XLOOKUP(G1,A2:A9,C2:C9)	$449.00	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.