#SPILL! Error when doing VLOOKUP?

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]