Why does VLOOKUP return #SPILL! in Excel 365?
Formula (Excel and Google Sheets)
=VLOOKUP(B2,$E$2:$F$4,2,FALSE)The lookup value is a whole column, so VLOOKUP tries to return a result for every row. Look up one cell and fill down: =VLOOKUP(B2,$E$2:$F$4,2,FALSE).
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
In Excel 365, =VLOOKUP(B:B,…) asks for a result for every row of column B: about a million of them. That can't spill into the sheet, so Excel shows #SPILL!. Looking up one cell, B2, returns one result, and filling down gives each ticket its team.
=VLOOKUP(B2,$E$2:$F$4,2,FALSE)Result"Billing"
B2"Kim"The value in B2.
$E$2:$F$43 rows × 2 cols: "Kim", "Billing", "Raj", "Tech", …A range: 3 rows × 2 columns.
VLOOKUP(B2,$E$2:$F$4,2,FALSE)"Billing"VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.- lookup_value
B2"Kim" - table_array
$E$2:$F$43 rows × 2 cols: "Kim", "Billing", "Raj", "Tech", … - col_index_num
2 - range_lookup
FALSE
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =VLOOKUP(B2,$E$2:$F$4,2,FALSE). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4) | Billing, Tech, Billing… | The XLOOKUP version, also one result per row. |
Common mistakes
=VLOOKUP(B2:B7,$E$2:$F$4,2,FALSE)in C2 alone is fine in Excel 365: it spills six results. Just don't fill it down as well.- Formulas from older workbooks that relied on picking the value from the same row may now spill. Excel adds @ to them to keep the old behavior.
- Inside an Excel Table, use
[@Agent]for the value on the same row.
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.
Why does VLOOKUP return #SPILL! in Excel 365
C2, look up the team for the agent in B2, one row at a time, and fill down to C7.Questions
Can one formula fill the whole column?
Yes, in Excel 365: =VLOOKUP(B2:B7,$E$2:$F$4,2,FALSE) in C2 only, with the cells below empty. Google Sheets needs ARRAYFORMULA around it.
Why did this work in older Excel?
Older Excel quietly used only the value on the same row (implicit intersection). Excel 365 returns every value instead.
Why do I still get #SPILL! from a formula that should spill?
Something is in the way: any value in the cells the result needs (C3:C7 here) blocks it. Clear those cells.
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.