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.
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.
