Forum Discussion
#SPILL! Error when doing VLOOKUP?
Hi Jennifer Corcoran ,
This is likely because your VLOOKUP is looking up multiple values in the first argument (the red text below).
=VLOOKUP(A:A,B:B,1,FALSE)
By looking up A:A you are asking Excel to lookup a million cells. In the past this worked because Excel didn't know how to lookup multiple values, so it threw all but one away. This behaviour was called "implicit intersection". With the introduction of Dynamic Arrays, Excel now supports looking up multiple values and no longer does implicit intersection silently. If there isn't enough space to return the values you will see the #SPILL error.
To modify your formula to return just a single value, you can use one of the following techniques:
1. Modify you formula to use the new implicit intersection operator @ to select one lookup value. =VLOOKUP(@A:A,B:B,1,FALSE)
2. Simply reference a single cell and copy down. =VLOOKUP(A2,B:B,1,FALSE)
Both work but my preferred option is 2 as it is the simplest.
Regards, Joe [Excel Team]
I really wish that you had posted a workbook.
I tried to imagine what your layout looked like, and decided you might have a four column named range called colors and a table named Table1 with a column header label of Color Number. I then created a formula on the same row as data in the table, but not part of the table. That's where I got a #SPILL error value.
The fix was to use this formula:
=VLOOKUP(Table1[@[Color Number]], colors, 4,FALSE)
Hi Brad,
thank you very much, I will ask my tutee in excel to try this. see 2 workbooks from me and her, she got the error, I did not.
.jpg){kind=link}
I moved the formula inside my Table1 like this, and it worked like shown below.
=VLOOKUP([@[Color Number]], colors, 4,FALSE)When I changed the table to have only a single row, your original formula worked. But as soon as I added a second row to that table, both formulas returned #SPILL error value. The fix is shown above.
Brad
my tutee made the changes and it shows #REF! Do you have any other suggestions? Thank you for your time. Stay Safe.
Do I have any other suggestions?
I most certainly do. Post a file that replicates the problem. Two rows of data are sufficient.
Screenshots are not sufficient. It must be an actual file that replicates the problem.
Brad
HI BradBrad Yundt
Thank you so much! I will reply as soon as I can contact my tutee to make this correction
