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]
Jane,
It doesn't matter whether the range is named Colors or colors--Excel formulas will recognize it because they are case insensitive.
If you look at Imani's screenshot issue 3.jpg, you will see that she had ='Colors Data'!$A$2:$D$31 in the "Name" field and ''=C:\Users\Exploring\Dropbox\Pearson..." in the "Refers to" field. Both entries are incorrect, and were the cause of the #REF! error values.
The Name field should contain a simple name like colors rather than a formula. Furthermore, the Refers to field should have a formula pointing to data in the same workbook like ='Colors Data'!$A$2:$D$31. My guess is that Imani copied either a worksheet or a range of cells from one workbook to another, and that's why named range colors kept pointing to the original source, which was now in a different workbook. Then when the resulting workbook is opened by somebody else on a different computer, Excel cannot follow the path to the data for named range colors, hence the #REF! error values.
To summarize the entire experience, although the original complaint was about the new #SPILL! error value, the underlying problems had nothing to do with dynamic arrays. Instead, there were problems with named ranges pointing to broken links and incorrect syntax for structured reference formulas in a Table. You would have had those same problems in Excel 2007, albeit with a different error value being returned.
I am really glad you posted workbooks that reproduced the issues. Without them, I would likely still be guessing about the underlying problems.
Brad Yundt Hi Brad,
This makes sense. I will save this for future reference, the information about named range and syntax and structured reference. Thank you very much for helping me and Imani.
Jane