Good evening, everyone.
I'm attempting to put together an Excel spreadsheet that can keep track of my video game collection. In it, I have a table and slicers that can filter the collection by console (SNES, Xbox, etc.) and update the tallies at the top of the spreadsheet to reflect what has been filtered.
The trouble that I'm running into now is how to tell Excel to also display the most valuable game (either overall or by specific console) by name. This is what I was able to come up with:
=INDEX([Name:],MATCH(Inventory[[#Totals],[Top Game:]],[2018 Value:],0),)
Whenever the table is filtered, a helper column (Top Game
returns the highest value game and the rest of the function returns the name associated with that value. It works well... until Excel runs into games that have the same value as another game. When that happens Excel returns a game name that is completely outside the filtered category.
I've been wracking my brain over this for nearly a week unable to find a solution. Is there some way to tell Excel to return the correct results? Or perhaps a way to tell Excel to not search the entire INDEX for a matching value, but only those in the selected filter or filters? Any help would be very deeply appreciated.
I'm attempting to put together an Excel spreadsheet that can keep track of my video game collection. In it, I have a table and slicers that can filter the collection by console (SNES, Xbox, etc.) and update the tallies at the top of the spreadsheet to reflect what has been filtered.
The trouble that I'm running into now is how to tell Excel to also display the most valuable game (either overall or by specific console) by name. This is what I was able to come up with:
=INDEX([Name:],MATCH(Inventory[[#Totals],[Top Game:]],[2018 Value:],0),)
Whenever the table is filtered, a helper column (Top Game
returns the highest value game and the rest of the function returns the name associated with that value. It works well... until Excel runs into games that have the same value as another game. When that happens Excel returns a game name that is completely outside the filtered category.I've been wracking my brain over this for nearly a week unable to find a solution. Is there some way to tell Excel to return the correct results? Or perhaps a way to tell Excel to not search the entire INDEX for a matching value, but only those in the selected filter or filters? Any help would be very deeply appreciated.
