Can I use whole-column references like A:A and B:B in XLOOKUP?

Yes. =XLOOKUP(D1, A:A, B:B) works and is the simplest way to guarantee matching sizes — both A:A and B:B contain the same 1,048,576 rows. Caveat: whole-column references scan more cells than necessary, so on very large workbooks performance can suffer. For most spreadsheets the speed difference is invisible. Excel Tables (Ctrl-T) give you the same size-guarantee with better performance.

Does "Array arguments are of different size" happen in Google Sheets?

Yes, Google Sheets' XLOOKUP has the identical constraint and returns the same #VALUE! error with similar wording when lookup_array and return_array sizes don't match. The fixes are identical: align the ranges, use whole-column references, or wrap the data in a named range.

Error diagnostic · Excel & Google Sheets

XLOOKUP `#VALUE!` — “Array arguments are of different size”

Q: What does "Array arguments to XLOOKUP are of different size" mean?

XLOOKUP returns #VALUE! whenever its lookup_array (the column or row it searches) and its return_array (the column or row it returns from) don't have the same number of cells in the matching direction. For a vertical lookup, both arrays must have the same number of rows. For a horizontal lookup, both must have the same number of columns. The arrays must also point the same direction — you can't mix one vertical with one horizontal slice.

Q: Why does XLOOKUP require lookup_array and return_array to be the same size?

XLOOKUP finds its match by position. When lookup_value is found at position N in lookup_array, the answer is the cell at position N in return_array. If the two arrays have different lengths, position N either doesn't exist on one side or maps to nothing — Excel raises #VALUE! rather than guess. This is also why XLOOKUP can return values to the left of the lookup column (unlike VLOOKUP) — the two ranges are independent and aligned only by row count.

Q: How do I fix "Array arguments are of different size" in XLOOKUP?

Count cells in both ranges, then make them equal. The fastest fix is to retype the formula and re-select the two ranges with the mouse, so Excel auto-fills the same row span. If both columns are full and have no extra rows, use whole-column references — =XLOOKUP(A2, B:B, C:C) — which always have the same length. For data that grows, use named ranges or convert the table to an Excel Table object so the structured references stay in sync.

Q: Why do I get #VALUE! when one array is a row and the other is a column?

XLOOKUP requires lookup_array and return_array to share orientation as well as size. If you pass A1:A10 (vertical) and B1:K1 (horizontal), Excel can't pair them positionally and returns #VALUE! — even though both have 10 cells. Fix: make both arrays vertical, or both horizontal. To rotate one of them in-formula, wrap it in TRANSPOSE: =XLOOKUP(lookup, A1:A10, TRANSPOSE(B1:K1)).

Last updated: June 2026

Excel throws #VALUE! with the exact message “The array arguments to XLOOKUP are of different size” whenever lookup_array and return_array point to ranges with different row counts (or different column counts for a horizontal lookup). The fix is mechanical: count the cells in both ranges and align them. Below: an interactive demo showing the bug, five common ways the mismatch sneaks in, and four fixes — in order of increasing robustness.

=XLOOKUP("E003", A2:A6, C2:C5)

	A — ID	B — Name	C — Dept
2	E001	Alice	Sales
3	E002	Bob	Eng
4	E003	Carol	Design
5	E004	Dan	Eng
6	E005	Eve	Sales
7	XLOOKUP RESULT — `A2:A6` (5 rows) ≠ `C2:C5` (4 rows)		#VALUE!

lookup_array · A2:A6 · 5 rowsreturn_array · C2:C5 · 4 rowsIn column, outside the range — the gap

If you see this …	What changed	The fix
`=XLOOKUP(A2, B2:B10, C2:C11)` → `#VALUE!`	Off-by-one — return range extends one row past lookup	`=XLOOKUP(A2, B2:B10, C2:C10)`
`=XLOOKUP(A2, B1:B10, C2:C10)` → `#VALUE!`	One range includes a header row, the other doesn't	`=XLOOKUP(A2, B2:B10, C2:C10)` — drop the header
`=XLOOKUP(A2, B2:B10, C2:K2)` → `#VALUE!`	One range is vertical, the other horizontal	`=XLOOKUP(A2, B2:B10, TRANSPOSE(C2:K2))`
Data grew, formula didn't	You added rows but ranges still point to the old end	Switch to whole-column `B:B, C:C` or an Excel Table
`=XLOOKUP(A2, MyIds, MyNames)` → `#VALUE!`	Named ranges have drifted apart	Name Manager → fix the ranges to share row count

01 · The mechanism

What the error actually means

XLOOKUP finds its answer by position. Mismatched sizes break positional alignment — and Excel refuses to guess.

XLOOKUP's contract is simple: when lookup_value matches position N in lookup_array, return the cell at position N in return_array. If the two arrays don't have the same number of cells along the matching axis, position N either runs off one end or has nothing on the other side. #VALUE! is Excel saying “I can't pair these ranges 1-to-1.”

The ruleROWS(lookup_array) == ROWS(return_array) for vertical lookups, COLUMNS(lookup_array) == COLUMNS(return_array) for horizontal ones. Both arrays must also share orientation.

A useful diagnostic: type =ROWS(B2:B10) and =ROWS(C2:C11) into two spare cells. The numbers should be equal. If they aren't, you've found the bug without having to read the formula.

02 · How the mismatch sneaks in

Five most common causes

In practice, most “different size” errors trace to one of these patterns.

Cause 1 — Off-by-one on the last row

You typed one range, then typed the second, and the second drifted by one row. Easiest mistake when authoring a formula manually instead of dragging the selection.

=XLOOKUP(A2, B2:B10, C2:C11) — 9 rows vs 10 rows → #VALUE!
Fix: re-select return_array by clicking the start cell and shift-clicking the end cell — Excel will fill the same row span.

Cause 2 — One range includes the header, the other doesn't

You meant to skip the header row. You did skip it — in one range. The other range still starts at row 1.

=XLOOKUP(A2, B1:B10, C2:C10) — 10 rows vs 9 rows. B1 is the header cell.
Fix: drop the header from both, or include it in both. =XLOOKUP(A2, B2:B10, C2:C10) is the common preferred form.

Cause 3 — Mixed orientation (vertical + horizontal)

One range walks down a column, the other walks across a row. Even if the cell counts match, XLOOKUP refuses to pair them — it requires the two arrays to share orientation.

=XLOOKUP(A2, B2:B11, C1:L1) — 10 vertical cells vs 10 horizontal cells. Both have 10 cells; orientation still wrong → #VALUE!.
Fix: TRANSPOSE one of them in-place — =XLOOKUP(A2, B2:B11, TRANSPOSE(C1:L1)).

Cause 4 — Data grew, formula didn't

You wrote the formula when there were 50 rows. The dataset is now 60 rows. Some XLOOKUPs got auto-updated by Excel's structured references; others didn't. Now your two ranges are unequal.

Fix: switch to whole-column references (B:B, C:C) so length is always equal — or, better, convert the data to an Excel Table (Ctrl+T). Table column references like Employees[ID] and Employees[Name] always resolve to the same number of rows.

Cause 5 — Named ranges that drifted

Named ranges look identical in the formula — MyIds, MyNames — so the size mismatch is invisible until you open Name Manager. Common when sheets are inherited from a teammate or when ranges were defined by separate INDIRECT formulas.

Fix: Formulas tab → Name Manager → click each name and check the Refers to formula. Make the row spans match. Or move the data to an Excel Table and use Table1[ID] column refs.

03 · Four ways to make it stay fixed

Four fixes, weakest to strongest

The first three patch the immediate formula. The fourth makes the class of error impossible.

Fix 1 — Manually align the ranges

Edit the formula and make the row counts match. Fast but fragile — the next person who adds a row will recreate the bug.

=XLOOKUP(A2, B2:B100, C2:C100)
Useful when the dataset is genuinely fixed-size (e.g. a closed report).

Fix 2 — Whole-column references

B:B and C:C both contain every cell in their column (1,048,576 rows in modern Excel). They're trivially the same size — the mismatch error becomes impossible.

=XLOOKUP(A2, B:B, C:C)
Caveat: large workbooks can feel slower because XLOOKUP technically scans more cells. For most sheets the difference is unnoticeable; if you can't feel it, use it.

Fix 3 — Named ranges or LET binding

Park the lookup and return ranges in named ranges anchored to a shared row span. Update one definition and every formula on the sheet stays in sync.

=XLOOKUP(A2, ids, names)
Where ids = Sheet1!$B$2:$B$100 and names = Sheet1!$C$2:$C$100. Or with LET: =LET(ids, B2:B100, names, C2:C100, XLOOKUP(A2, ids, names)).

Fix 4 — Convert to an Excel Table (the durable fix)

Excel Tables (Ctrl+T) make structured column references that can't drift out of sync. Adding a row extends every column at once. The size-mismatch class of bug disappears.

=XLOOKUP([@ID], Employees[ID], Employees[Name])
Both column refs return the same number of rows by construction. Same syntax works in Google Sheets if you wrap the data in a named range (Sheets has no exact Table equivalent yet).

04 · Adjacent errors

Other XLOOKUP errors and how to tell them apart

#VALUE! from size mismatch is one of several errors XLOOKUP can raise. The error text differs — read it carefully before fixing.

Error	Excel's wording	What it actually means
`#VALUE!`	“The array arguments to XLOOKUP are of different size”	`lookup_array` and `return_array` differ in rows / columns / orientation. This page.
`#N/A`	“Value not available”	`lookup_value` isn't in `lookup_array`. Different problem — set the 4th argument `if_not_found` to handle it. See the if_not_found guide →
`#REF!`	“Invalid cell reference”	One of the ranges was deleted, or the formula was copied across sheets and lost its anchor.
`#NAME?`	“Unrecognized text in a formula”	Typo in `XLOOKUP`, or you're on Excel 2019 / 2016 where XLOOKUP doesn't exist.
`#SPILL!`	“Spill range isn't blank”	Your XLOOKUP returns more than one cell (when `return_array` is multi-column), but adjacent cells are occupied. Clear the cells in the spill path.

05 · Where this rule applies

Excel & Sheets compatibility

The size-mismatch rule is identical across every platform that ships XLOOKUP. So is the fix.

Platform	XLOOKUP support	Size-mismatch behaviour
Excel for Microsoft 365 (any plan)	✅ Yes	`#VALUE!` with the literal error message
Excel 2021	✅ Yes	Same — `#VALUE!`
Excel 2019 / 2016	❌ No XLOOKUP at all	You get `#NAME?` not `#VALUE!`. Use `INDEX/MATCH` instead.
Excel for the web	✅ Yes	Same as desktop
Google Sheets	✅ Yes (since Aug 2022)	Same — `#VALUE!` with similar wording about array sizes
LibreOffice Calc	✅ Yes (since 7.3)	`#VALUE!`

The XLOOKUP main reference and adjacent error topics on this site.

06 · Quick answers

FAQ

6.01What does “Array arguments to XLOOKUP are of different size” mean?▸

XLOOKUP returns #VALUE! whenever lookup_array and return_array don't have the same number of cells along the matching axis. For a vertical lookup, both must have the same number of rows. For a horizontal lookup, both must have the same number of columns. The arrays must also share orientation — you can't mix one vertical slice with one horizontal slice.

6.02Why does XLOOKUP require lookup_array and return_array to be the same size?▸

XLOOKUP finds its answer by position. When lookup_value matches at position N in lookup_array, the result is the cell at position N in return_array. Different array lengths break that 1-to-1 mapping. This same design is what lets XLOOKUP return values to the left of the lookup column (unlike VLOOKUP) — the two ranges are independent and aligned only by row count.

6.03How do I fix “Array arguments are of different size” in XLOOKUP?▸

Count cells in both ranges (a quick =ROWS(B2:B10) and =ROWS(C2:C11) in two spare cells will show you the discrepancy), then make them equal. The fastest fix is to retype the formula and re-select the two ranges with the mouse, so Excel auto-fills the same row span. For datasets that grow, use whole-column references (B:B, C:C) or convert the data to an Excel Table.

6.04Can I use whole-column references like A:A in XLOOKUP?▸

Yes. =XLOOKUP(D1, A:A, B:B) works and is the simplest way to guarantee matching sizes — both A:A and B:B contain the same 1,048,576 rows in modern Excel. The downside is performance on very large workbooks; for most sheets the difference is invisible. If you can't feel the slowdown, use it.

6.05Does this error happen in Google Sheets too?▸

Yes. Google Sheets' XLOOKUP enforces the same constraint and produces an equivalent #VALUE! with similar wording about array sizes. Every fix on this page works on both platforms.

6.06Why do I get #VALUE! when one array is a row and the other is a column?▸

XLOOKUP requires lookup_array and return_array to share orientation as well as size. If you pass A1:A10 (vertical) and B1:K1 (horizontal), Excel can't pair them positionally and returns #VALUE! — even though both have 10 cells. To fix without restructuring your data, wrap one in TRANSPOSE: =XLOOKUP(lookup, A1:A10, TRANSPOSE(B1:K1)).

XLOOKUP #VALUE! — “Array arguments are of different size”