#SPILL! error - spill range is too big

[Paul_Taylor5]

Hi, 

I am trying a vlookup document but really struggling to solve a vlookup error. It is the first time I have really used this and the formula looks to be right but I keep getting #SPILL! error message. Formula used is -   =VLOOKUP(B:B,Sheet1!A:B,2,FALSE)

[#2]

@Paul_Taylor5 

#SPILL! error - Extends beyond the worksheet's edge

There is an often misunderstood method of creating VLOOKUP formulas by over specifying the lookup_value argument. Before dynamic array capable Excel, Excel would only consider the value on the same row as the formula and ignore any others, as VLOOKUP expected only a single value. With the introduction of dynamic arrays, Excel considers all the values provided to the lookup_value. This means that if an entire column is given as the lookup_value argument, Excel will attempt to lookup all 1,048,576 values in the column. Once it's done, it will attempt to spill them to the grid, and will very likely hit the end of the grid resulting in a #SPILL! error.  

For example...

Hope I was able to help you with this info.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.