SOLVED
Home

#SPILL! Error when doing VLOOKUP?

%3CLINGO-SUB%20id%3D%22lingo-sub-284690%22%20slang%3D%22en-US%22%3E%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20just%20updated%20my%20Office%20365%20(Mac)%20with%20the%20latest%20Excel%20updates%20yesterday%20and%20for%20some%20reason%20can%20no%20longer%20do%20a%20standard%20vlookup%20formula.%26nbsp%3BThe%20results%20in%20the%20cell%20where%20I%22m%20trying%20to%20do%20lookup%2C%20return%20with%20%22%23SPILL!%22%20I've%20tried%20doing%20the%20lookup%20several%20times%20and%20no%20luck.%26nbsp%3BDoes%20anyone%20know%20what%20this%20response%20means%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-284690%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284694%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284694%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jannifer%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20you%20happy%20to%20have%20most%20modern%20Excel%20with%20Dynamic%20arrays.%20There%20are%20few%20reason%20for%20the%20SPILL%20error%2C%20more%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2F-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2F-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023%3C%2FA%3E%20and%20about%20dynamic%20array%20first%20the%20search%20returned%20me%20is%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Ffunctions%2Fdynamic-array-formulas-spill-ranges%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelcampus.com%2Ffunctions%2Fdynamic-array-formulas-spill-ranges%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-653927%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-653927%22%20slang%3D%22en-US%22%3EI%20am%20having%20the%20same%20issue%20but%20on%20a%20PC%2C%20recent%20update.%20I%20was%20not%20able%20to%20use%20the%20below%20recommendation%2C%20as%20it%20did%20not%20provide%20any%20useful%20guidance.%20Help%2C%20I%20use%20VLook-up%20all%20the%20time%20and%20this%20is%20creating%20work%20stoppage!!%20Please%20let%20me%20know%20if%20you%20have%20learned%20of%20any%20other%20suggestions%20to%20fix%20the%20Spill%20error.%3CBR%20%2F%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753834%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F350611%22%20target%3D%22_blank%22%3E%40Ljohnson329%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExperiencing%20the%20same%20issue.%20And%20not%20able%20to%20find%20the%20reason%20behind%20that%20and%20also%20not%20able%20to%20get%20the%20solution%20anywhere.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753985%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376035%22%20target%3D%22_blank%22%3E%40utkarsh0103%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20post%20a%20workbook%20that%20demonstrates%20the%20problem.%20If%20the%20problem%20is%20reproduced%20at%20my%20end%20(I%20have%20multiple%20versions%20of%20Mac%20and%20Windows%20Excel)%2C%20I%20can%20either%20suggest%20a%20workaround%20%26amp%3B%20explanation%2C%20or%20else%20report%20it%20to%20Microsoft%20developers%20as%20a%20bug.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772811%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772811%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374823%22%20target%3D%22_blank%22%3E%40Brad_Yundt%3C%2FA%3E%2C%20I%20am%20getting%20the%20same%20error.%20Would%20appreciate%20some%20help%20but%20cannot%20find%20an%20option%20to%20upload%20the%20workbook%20here.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772828%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381832%22%20target%3D%22_blank%22%3E%40saurajyoti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20I%20see%20in%20the%20Message%20box%20is%20shown%20below.%20The%20Browse...%20button%20at%20the%20bottom%20left%20is%20what%20I%20use%20to%20attach%20files.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20that%20button%2C%20then%20my%20email%20address%20is%20first%20initial%20last%20name%20at%20my%20ISP%20alum%20dot%20mit%20dot%20edu.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBrad%20Yundt%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124424i2247AEE54BC45CB1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782191%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20VLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782191%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F240435%22%20target%3D%22_blank%22%3E%40Jennifer%20Corcoran%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20likely%20because%20your%20VLOOKUP%20is%20looking%20up%20multiple%20values%20in%20the%20first%20argument%20(the%20red%20text%20below).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(%3CFONT%20color%3D%22%23ff0000%22%3EA%3AA%3C%2FFONT%3E%2CB%3AB%2C1%2CFALSE)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20looking%20up%20%3CFONT%20color%3D%22%23ff0000%22%3EA%3AA%3C%2FFONT%3E%26nbsp%3Byou%20are%20asking%20Excel%20to%20lookup%20a%20million%20cells.%20In%20the%20past%20this%20worked%20because%20Excel%20didn't%20know%20how%20to%20lookup%20multiple%20values%2C%20so%20it%20threw%20all%20but%20one%20away.%20This%20behaviour%20was%20called%20%22implicit%20intersection%22.%20With%20the%20introduction%20of%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FPreview-of-Dynamic-Arrays-in-Excel%2Fba-p%2F252944%22%20target%3D%22_self%22%3EDynamic%20Arrays%3C%2FA%3E%2C%20Excel%20now%20supports%20looking%20up%20multiple%20values%20and%20no%20longer%20does%20implicit%20intersection%20silently.%20If%20there%20isn't%20enough%20space%20to%20return%20the%20values%20you%20will%20see%20the%20%23SPILL%20error.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20modify%20your%20formula%20to%20return%20just%20a%20single%20value%2C%20you%20can%20use%20one%20of%20the%20following%20techniques%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Modify%20you%20formula%20to%20use%20the%20new%20implicit%20intersection%26nbsp%3Boperator%20%40%20to%20select%20one%20lookup%20value.%26nbsp%3B%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%3DVLOOKUP(%3CFONT%20color%3D%22%23ff0000%22%3E%40%3C%2FFONT%3E%3C%2FSPAN%3E%3CFONT%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23ff0000%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20color%3D%22%23ff0000%22%3EA%3AA%3C%2FFONT%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%2CB%3AB%2C1%2CFALSE)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E2.%20Simply%20reference%20a%20single%20cell%20and%20copy%20down.%26nbsp%3B%3CSPAN%20style%3D%22background-color%3A%20%23ffffff%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20display%3A%20inline%3B%20float%3A%20none%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%3DVLOOKUP(%3C%2FSPAN%3E%3CFONT%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23ff0000%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20color%3D%22%23ff0000%22%3EA2%3C%2FFONT%3E%3CSPAN%20style%3D%22background-color%3A%20%23ffffff%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20display%3A%20inline%3B%20float%3A%20none%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%2CB%3AB%2C1%2CFALSE)%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBoth%20work%20but%20my%20preferred%20option%20is%202%20as%20it%20is%20the%20simplest.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegards%2C%20Joe%20%5BExcel%20Team%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jennifer Corcoran
Microsoft

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? 

7 Replies

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/

I am having the same issue but on a PC, recent update. I was not able to use the below recommendation, as it did not provide any useful guidance. Help, I use VLook-up all the time and this is creating work stoppage!! Please let me know if you have learned of any other suggestions to fix the Spill error.
Thank you!

@Ljohnson329 

Experiencing the same issue. And not able to find the reason behind that and also not able to get the solution anywhere.

@utkarsh0103 

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.

@Brad_Yundt , I am getting the same error. Would appreciate some help but cannot find an option to upload the workbook here.

@saurajyoti 

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

 

image.png

Solution

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]

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies