Fix VLOOKUP #SPILL! Error | The Formula you have entered may spill beyond the edges of the worksheet

In the video I show you how to fix the #SPILL! error when using VLOOKUP. The #SPILL! error indicates that there is not enough cells for the formula to display its results in. Excel 365 supports dynamic array formulas where a single formula is capable of displaying multiple results. The array of results are spilled into surrounding cells.

Excel 365 warns users that get this error that the formula they have entered may spill beyond the edges of the worksheet. This warning occurs when the user selects an entire column in the lookup_value argument and the VLOOKUP has been typed into a row other than row 1.

Excel offers to correct the formula with an '@' symbol in front of the lookup_value. The '@' symbol forces an implicit intersection where multiple results are collapsed down into one. The result that is displayed is the one that shares the same row as the formula.
------------------------

[kingpinn]

that @ saved me. been trying to find a solution for the last 2 days

[Mohammed-wzen]

Thank you so much! It was hard to find a video that explained the #spill error without requiring us to fill the array of data below the cell. However, you mentioned that we could use the @ symbol to resolve the error, which worked for me. Big thanks.

[jramaswamy]

Excellent lesson. Simple and straightforward. Thank you.

[mnrqt]

Thank you, the last tip to select the lookup value as a single cell solved the issue for me.

[ScottRuss-gu]

Thanks for this quick, clear explanation! This was really helpful.

[bh]

Miss the old Excel never had the Spill error

[hasanghassan]

You have fixed my issue, thanks Chester

[myintmoe-be]

It's great & useful answer for me. Thank you very much.

[wayneedmondson]

Good tips! Thanks Chester. Thumbs up!!

[patrickschardt]

It’s a good thing Microsoft only has this error if you reference an entire column (and assuming whole rows). Otherwise, we would get this error all the time since a table could become the whole column.

Good tips to get around it.

[itxneh]

IN FIRST I'M NOT WATCH THIS VIDEO COMPLETELY I'M GOING ON ANOTHER VIDEO BUT I DON'T GAVE ANY SOLUTION AFTER THAT IS IN HALF ON MY SCREEN I'M WATCH COMPLETELY & SOLUTION IS VERY SUCCESSFULL

[AB-qbpt]

chester if we would like to make a statue, the cement wont be enough !

[lmajiedmary]

I have been to a vast array of teachers on YouTube on the subject of excel and have found your site to be, by far, the best. Thank you so much for the simplification in your lessons. Now that I have found you, I have come to you for quite a few issues I have come across and you always give me exactly what I need!! You are now my "go to" person!!! Thank you, thank you!!

[IvanCortinas_ES]

Excellent explanation Chester. It is a symbol that we find when opening files treated before the spilled-array arrived. Thank you!!!