Index Match a unique list with additional criteria.

[AnalysisParalysis]

Hi guys, I have some formula which calculates a list of unique names based on a table:


=IFERROR(INDEX(Table_Query_from_Sales2_6[OPENED_BY],MATCH(0,COUNTIF($D$5:D5,Table_Query_from_Sales2_6[OPENED_BY]),0)),"")


Table_Query_from_Sales2_6[OPENED_BY] = List of names (table reference)

This is entered in to D4.


In the table where the names are there is also a list of months and years. I’m looking to create a unique list of staff who were active in each month.


IE i want to add 2 additional criteria month=2, year=2012.


Any help would be lovely.


Ta

 

[Luke M]

Can you use a PivotTable instead? It would easily & quickly create a list of unique entries for any set of criteria you have.

 

[AnalysisParalysis]

The criteria needs to be dynamic. It is for a performance dashboard.


I have a series of filters (Month, Year, Team).


Thanks for the suggestion.

 

[Luke M]

Not sure why that is a problem. You can easily change the filters on a PivotTable.

Or, if you want to try and get fancy, you could have the PivotTable is a sheet somewhere simply summarizing all the data, and then use a dynamic GETPIVOTDATA function to retrieve the info of interest.