How to fix the #VALUE! error in XLOOKUP
Formula (Excel and Google Sheets)
=XLOOKUP(G1,A2:A9,C2:C9)Make the lookup and return ranges the same size: =XLOOKUP(G1,A2:A9,C2:C9), not A2:A9 with C2:C8.
- Result on the example
- $129.00 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- 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
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.
=XLOOKUP(G1,A2:A9,C2:C9)Result129
G1"P-107"The value in G1.
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 8 cells in one column.
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …A range: 8 cells in one column.
XLOOKUP(G1,A2:A9,C2:C9)129XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G1"P-107" - lookup_array
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", … - return_array
C2:C98 rows × 1 col: $39.99, $249.00, $89.50, $449.00, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(G1,A2:A9,C2:C9). We ran it in Google Sheets mode on the same data and it returns $129.00.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(G1,A:A,C:C) | $129.00 | 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)) | $129.00 | 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) | #VALUE!Different result | 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".
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.
Fix the #VALUE! error in XLOOKUP
G2, return the price of the SKU in G1, with lookup and return ranges of the same 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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.