Forum Discussion
SPILL error
Microsoft, in the newest revision of Excel, has ( as I understand it) gone entirely to Dynamic Array functions, and that means that they'll return more than one answer if indeed more than one fits the criterion, in this case, of VLOOKUP. And if there are multiple rows that satisfy a function, then those multiple rows will "spill" into adjacent rows, unless something is blocking them, in which case you'll get a Spill Error.
What you're describing as the approach you've been taking sounds (pardon me for saying it) somewhat odd to begin with. Maybe I'm just not following it, but I'm puzzled by your description of "filtering on the #NA each time and then trying other VLOOKUPs to find the data"?
Let me suggest that you watch the video connected to this link, which introduces the concept of Dynamic Arrays, and some of the new Dynamic Array functions. One of them is FILTER, and it may be that FILTER is what you should start using instead of VLOOKUP. https://www.youtube.com/watch?v=9I9DtFOVPIg
If you'd like, though, I or somebody else could be of more help if you'd post a copy of your actual spreadsheet, so long as it doesn't contain any confidential or private information.
mathetes thank you for your response. I will check out the video. I also wanted to try and explain the filtering on #N/A that I do. I cant share my file because its got confidential info. So, I have several hundreds (sometimes thousands) of items
I need to look up in a spreadsheet and pull in data from other sources. The data I need to pull in comes from multiple sources, so I do my first look up against my first source and fast fill that V-look up down. I go back to top of column and filter on all the #N/A, clear the formula, and create a new v-look up against my second source. I might need to do this like 3 or 4 times before I'm able to pull in all the info I need. Sometimes I even need to do the v-look up against a different columns in my main spreadsheet. now I cant do multiple V-look ups if there is data filtered out within that column because of this new spill method =(
