Thread: Looking up names matching multiple criteria along with duplicates

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

ForumPost1.xlsx

2. What is the formula that you have so far?

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

4. Originally Posted by Nagaraj475
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
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.

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

6. Originally Posted by Roger Haney
(Could you not sort by the "Survey" Column initially, which seems to be easiest ? )
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

7. Formula Modification

Originally Posted by Nagaraj475
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
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)))

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

9. Originally Posted by Roger Haney
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)))
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