INDEX/MATCH and #SPILL! Error workaround?

[tshoup]

I have been using the conjunction of INDEX and MATCH formulas for nearly 6 years now and it has worked PERFECT for auditing data up until Microsoft did its recent update with "Dynamic Arrays" and the new feature of "Spill." These recent updates have presented 2 issues (possibly more) with the way that I have been using the formula compared to how it works now. If there is possibly a workaround, I am unaware of such that can fix my issues. I am just seeing of anyone has been able to figure any of these out yet.

Here is a generic set up of my data.
Sheet "Audit Main" would have variations of data in Column E:E (I use multiple other ranges but this is just one example). The data in E:E is basic numbers (2, 128, 346, 501, etc.) and are asset numbers. However, there are three separate categories/groups of assets to where these numbers are separated (Truck and Driver, Other Equipment, IT Equipment). The Audit Main sheet is maintained on one system, the data is exported, and is essentially a master list of assets. Each asset category/group are individually maintained in separate program/list, the data is exported, and is essentially is own master list. Each list has separate information that pertains to the induvial asset number (purchase date, purchase value, description, area/division, status, depreciation, tax class, etc). In auditing this information, I will combine the 4 individual list into one work book and will put each list on its own sheet. I will use the Asset Number as my baseline "MATCH" identifier and will use it to "INDEX" multiple arrays of information between the respective sheets.

Issue #1
Before:
I was able to select an entire cell range(A:A, N:N, J:J, etc) when "Indexing" and "Matching." Example =INDEX('Truck and Driver'!A:A,MATCH('Audit Main'!E:E,'Truck and Driver'!N:N,0)). Before the update there was no issue with the formula returning the data that I was Indexing and Matching.
Now:
I get a #SPILL! error that states my spill range is too big. I would assume that with all the programing knowledge that Microsoft has they could accommodate the rows of data that are "blank" and the program would be smart enough to ignore the blank values when trying to "SPILL" the data based on the formula.

Issue #2.
Before:
I could apply my formula: =INDEX('Truck and Driver'!A:A,MATCH('Audit Main'!E:E,'Truck and Driver'!N:N,0)) to the entire list of information on the "Audit Main" sheet and have it reference the three separate categories that were on their own individual sheets (Truck and Driver, Other Equipment, IT Equipment). I would start with "Truck and Driver" in the formula for the assets that pertained to the "Truck and Driver" list. For assets that are for a different category, I filter by that category and I just change the formula to refer to the respective category list, ie "Other Equipment" and change the reference points to where they exist on that respective sheet and fill the formula (I understand that I could also use the "INDIRECT" formula for this as well, but for specific purposes on this I don't). I would filter for "IT Equipment" and do the same. The formula worked just fine and would allow me to accommodate multiple variations of categories based on one master list of data.
Now:
When I apply the formula to the column where I want the "Indexed" values returned, I then go to filter by my category to change the reference sheet and reference point......Excel automatically thinks that I want this changed for ALL categories and then gives me the #SPILL! error. It even returns values in blank cells that have not corresponding data to even look up. I will not allow me to filter by category and change the sheet reference based on that specific category. This is causing me to have to make separate columns for each category and each set of indexed values that I have needing to return. BEYOND FRUSTRATING.

Issue #3
Pertains to Issue #2. When I try to filter to change my formula reference sheet/points, Excel looks at the existing retrieved data in that column and treats the existing information as "data in the spill range." When in fact it is not "data in the spill range." It will not accommodate filtering and changing the formulas reference location given there is a variation of where indexed data exist.

If anyone knows of a work around or what I may be doing wrong I am grateful for your input.

 

[tshoup]

You should not have the sorting problem if you are using Fluff's formula from post #6. The problem is caused by using the sheet name in the cell reference when it refers to the same sheet the formula is on.

Thanks Rory. I will try adjusting that and see what it does. When formulating a formula and toggling between sheets, the formula automatically adds each respective sheet name in the formula as you click between sheets. It is just faster to leave it in when typing out the formula. However, I will adjust it and see if that works that way I need it to. Again, thanks for the help!

 

[smigaud]

This was very frustrating for me but I found a solution for Index/Match. I assume it will work for vlookup as well.

=Index([array],Match(@A:A,BB,0))

adding the "@" symbol in that one spot cleared all my spill errors and even worked when pulling data from multiple sheets. It sounds like the update causes excel to look at the header of the column causing the #spill! error.

 

[EWek1]

yes, the @ does work. Please note that I also figured out that instead of using A:A, like in your example, just put A1 (or whatever the first cell in the column you want is) and it will work also. The spill is related to it trying to apply to all cells at once. I do not think it's related to the header of the column, rather it is trying to create / operate on an array. Replacing full column references like A:A with just the initial cell, A2, works. Also, as you found, using @A:A works in the same manner, I believe because it's like using an anchor like $ in your references.

 

[MothShutter]

yes, the @ does work. Please note that I also figured out that instead of using A:A, like in your example, just put A1 (or whatever the first cell in the column you want is) and it will work also. The spill is related to it trying to apply to all cells at once. I do not think it's related to the header of the column, rather it is trying to create / operate on an array. Replacing full column references like A:A with just the initial cell, A2, works. Also, as you found, using @A:A works in the same manner, I believe because it's like using an anchor like $ in your references.

setting up the data sources as tables with Insert/Table and giving it an appropriate name, you can then refer to the field headers rather than cell reference. There is slight performance slowing but by far easier to read the formula.