# How to fix the #VALUE! error in XLOOKUP

Make the lookup and return ranges the same size: =XLOOKUP(G1,A2:A9,C2:C9), not A2:A9 with C2:C8.

**Excel:** `=XLOOKUP(G1,A2:A9,C2:C9)` (Excel 2021, 2024 and Microsoft 365)

**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-107 |
| 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: `=XLOOKUP(G1,A2:A9,C2:C9)`

Result: $129.00

## How it works

XLOOKUP pairs each row of the lookup range with the same row of the return range, so both must have the same number of rows (or columns). `A2:A9` with `C2:C8` is one row short and returns #VALUE!. With matching ranges it finds P-107 and returns 129.

## Other ways

- `=XLOOKUP(G1,A:A,C:C)`: Whole columns are always the same size and include new rows. The header row gets searched too, which is harmless here.
- `=INDEX(C2:C9,MATCH(G1,A2:A9,0))`: INDEX/MATCH doesn't compare the sizes. That avoids the error, but misaligned ranges then return the wrong row silently.
- `=XLOOKUP(G1,A2:A9,C2:C10)`: Shown for contrast: the return range is one row longer. (#VALUE!, because the ranges have 8 and 9 rows.)

## Common mistakes

- Adding rows below the end of only one range is the usual way they drift apart. An Excel Table or whole columns keep them in step.
- A lookup value that is a range, like `XLOOKUP(G1:G3,…)`, spills several results; that is not a size error.
- Google Sheets reports it as "Array arguments to XLOOKUP are of different size".

## Questions

**Can the return range have several columns?**

Yes, with the same number of rows: `=XLOOKUP(G1,A2:A9,B2:D9)` returns three values side by side.

**What does #N/A from XLOOKUP mean instead?**

No match was found. Check for extra spaces and numbers stored as text.

**Why does XLOOKUP return #NAME?**

Your Excel version doesn't have XLOOKUP (Excel 2019 and earlier). Use INDEX/MATCH or VLOOKUP.

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-value-error