Forum Discussion

justwork's avatar
justwork
Copper Contributor
Jan 14, 2020

Disable Spill in vlookup

Is there a way to disable or turn off spill?  Spill was introduced to my version of O365 Excel today and vlookup is failing to operate as it has prior to the update.

 

In the image Sheet1 below I have a very simple vlookup in cell C5 and I only want a result in C5 which should be a value of "Cell C5" (see image Sheet2 to validate) but as you can see due to spill the field I want populated is on row 8 instead of row 5.  This results in incorrect data.

 

Moving the formula to row 2 is not a solve as this overly simplified example is to show the issue experienced.  My actual data sets are much larger and span many columns across multiple sheets resulting in hundreds of thousands of errors.

 

Any help is appreciated.

 

Sheet1

Sheet1

 

Sheet2 - This image is of sheet2 so you can see what the text is.

Sheet2

excel
office 365

5 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jan 14, 2020

    justwork 

    That is because your VLOOKUP() is wrong. You entered a cell range instead of a single cell reference for the search criteria. And that tells Excel to spill.

    Just change the range to a single cell reference and everything is good.

     

    • justwork's avatar
      justwork
      Copper Contributor
      Jan 14, 2020

      Detlef_Lewin 

      As I said this is a simplified example.  I use vlookup to find the match in the range, the example shows how it does not return the result to the correct line like it used to (row 5 should have the value in row 5), now it just moves the lines like shown in row 8. 

       

      I opened my real work file in Excel just now and noticed a new pop up appear that wasn't there this morning telling me that "We've upgraded Excel's formula language and, as a result, you might notice the @ operator in some formulas. Your formulas will behave the same as they always have.".  My formula now has the @ symbol as shown "=IFERROR(VLOOKUP(@$O$2:$O$997853,'KEY-Desc'!$B$1:$E$1000000,3,FALSE),"REMOVEME")" so maybe it will work.  Were going to give it a try and see, I have no idea what the at symbol does as this is new to me today.

Resources