User Profile
justwork
Copper Contributor
Joined Jan 14, 2020
User Widgets
Recent Discussions
Re: Disable Spill in vlookup
Patrick2788 I have been playing around with a small data set and it seems to be working as expected now. As you can see row 17 and 22 are examples where its not spilling and I validated that the data is accurate. The whole issue I had revolves around wanting to copy the entire formula and paste it into a cell so it will run, this is now working as expected.Re: Disable Spill in vlookup
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.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 Sheet2 - This image is of sheet2 so you can see what the text is.
