Nov 09 2018 09:03 AM - edited Nov 09 2018 09:04 AM
I just updated my Office 365 (Mac) with the latest Excel updates yesterday and for some reason can no longer do a standard vlookup formula. The results in the cell where I"m trying to do lookup, return with "#SPILL!" I've tried doing the lookup several times and no luck. Does anyone know what this response means?
Nov 09 2018 09:14 AM
Hi Jannifer,
It looks like you happy to have most modern Excel with Dynamic arrays. There are few reason for the SPILL error, more is here https://support.office.com/en-us/article/-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023 and about dynamic array first the search returned me is https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/
May 28 2019 03:39 PM
Jul 14 2019 02:03 AM
Experiencing the same issue. And not able to find the reason behind that and also not able to get the solution anywhere.
Jul 14 2019 10:00 AM
Please post a workbook that demonstrates the problem. If the problem is reproduced at my end (I have multiple versions of Mac and Windows Excel), I can either suggest a workaround & explanation, or else report it to Microsoft developers as a bug.
Jul 24 2019 08:36 PM
Jul 24 2019 09:12 PM
What I see in the Message box is shown below. The Browse... button at the bottom left is what I use to attach files.
If you don't have that button, then my email address is first initial last name at my ISP alum dot mit dot edu.
Brad Yundt
Jul 31 2019 12:03 PM - edited Jul 31 2019 04:27 PM
SolutionHi @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]
Jan 20 2020 02:24 AM
@JoeMcDaid I signed up to this website just to thank you for offering the solution, you are officially the saviour of many lives.
Jan 27 2020 11:23 PM
@JoeMcDaid thank you so much! Wish Excel had given us the heads up, been scratching my head for days!!
Mar 11 2020 08:12 PM
Mar 11 2020 08:46 PM
Post a file and show the type of results you want. That way, an appropriate formula can be suggested.
Brad Yundt
Apr 11 2020 05:31 PM
Hi Joe and Brad.
I am getting a spill error with the function
=VLOOKUP ([Color Number], colors, 4,FALSE)
Where should I put the @ (implicit intersection operator) to stop the #SPILL ERROR
I am looking up an item's color number in the colors table to get the year that color was retired if applicable, in the 4th column of the table. the function returns 0 if the retired cell in the lookup table is blank
Apr 11 2020 05:49 PM
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)
Apr 11 2020 05:56 PM
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.
Apr 11 2020 06:06 PM
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
Apr 11 2020 06:13 PM
HI Brad@Brad Yundt
Thank you so much! I will reply as soon as I can contact my tutee to make this correction
Apr 11 2020 08:08 PM
my tutee made the changes and it shows #REF! Do you have any other suggestions? Thank you for your time. Stay Safe.
Apr 11 2020 10:17 PM
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
Apr 12 2020 12:39 PM
Hi Brad@Brad Yundt
Enclosed is the workbook with the spill error in the year retired column. Thank you for looking at this.
Jane
Jul 31 2019 12:03 PM - edited Jul 31 2019 04:27 PM
SolutionHi @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]