Looking up names matching multiple criteria along with duplicates

I have a list of contact center employees with certain number of surveys and certain CSAT(Customer Satisfaction score for surveys). I figured out way to find maximum surveys and respective CSAT achieved for that particular number of surveys. I also have found formula to get names of employees matching max survey and max CSAT criteria.The problem with the formula is it omits duplicate entries. As in the example,there are 2 employees with same scores but the formula lists only employee 10. How do I improvise the formula to list all employees including duplicates? I have attached the sample data file and a screenshot below.

View attachment ForumPost1.xlsx

What is the formula that you have so far?

For finding top largest values using formula =LARGE(Survey,A2) Survey is named range

For finding maximum CSAT for a particular large value, using formula =MAX((Survey=F2)*CSAT)

CSAT is a named range here.
To get employee names matching highest survey and highest CSAT criteria using formula =INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0))

All these formulae are included in the attached file. Thank you for looking into. Thanks for your time.

Regards,
Nagaraj

Nagaraj475 said:

For finding top largest values using formula =LARGE(Survey,A2) Survey is named range

For finding maximum CSAT for a particular large value, using formula =MAX((Survey=F2)*CSAT)

CSAT is a named range here.
To get employee names matching highest survey and highest CSAT criteria using formula =INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0))

All these formulae are included in the attached file. Thank you for looking into. Thanks for your time.

Regards,
Nagaraj

Click to expand...

One option is to simplify the arrangement of your Data (if this is allowed ). Put your Emp. names in numerical order in Column H. In G2 type =D2 and fill down. In F2 type =C2 and fill down and then F9 to see results. if you need the "Survey" results to be in descending order as in your example, then you can copy the table range and "Past Special" values and then sort descending by that Column. if this in not an option ( re-arranging your Emp. names ), then this is still possible by a formula. Please advise.

(Could you not sort by the "Survey" Column initially, which seems to be easiest ? )

Roger Haney said:

(Could you not sort by the "Survey" Column initially, which seems to be easiest ? )

Click to expand...

Thank you, Roger Haney for looking into this and sharing your suggestion. Sorting multiple levels is easier but, wanted a way to getting results without altering or modifying original data.

Thanks,
Nagaraj

Formula Modification

Nagaraj475 said:

Thank you, Roger Haney for looking into this and sharing your suggestion. Sorting multiple levels is easier but, wanted a way to getting results without altering or modifying original data.

Thanks,
Nagaraj

Click to expand...

The following appendage to your original formula will compensate for duplication, while also leaving your original data structure intact : =IF(AND(F2=F3,G2=G3),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)-1),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)))

As a further refinement in case there are multiple duplicates beyond two, then this formula : =IF(AND(F2=F3,G2=G3),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)-(COUNTIF(F2:$F$11,F2)-1)),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)))

Roger Haney said:

As a further refinement in case there are multiple duplicates beyond two, then this formula : =IF(AND(F2=F3,G2=G3),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)-(COUNTIF(F2:$F$11,F2)-1)),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)))

Click to expand...

Thank you Roger Honey for sharing your expertise and helping. Many thanks to Excelguru.ca and team for helping get answer for many Excel related queries from the past 7 years.

Thanks,
Nagaraj