Excel Index Match Large If

Hi All,

Hope that you can help - this has stumped me for a few hours now and I can't seem to find an answer on the web. I'm looking to find the name of a job with the greatest count, based on a few criteria. Please see the attached example sheet I put together.

In detail, I'm looking to find the job title with the greatest (1st largest, 2nd largest, 3rd largest, etc) count of employees who fall between a certain age range (e.g., 40 - 50 years old) and a certain tenure (e.g., 5 - 15 years of experience), who work in a certain location (e.g., WA or Washington).

If there are 10 Cooks, 3 Cleaners, and 2 Drivers who are between 40-50 years old, with 5-15 years of experience, who are in WA, I'm looking for a formula(s) that can identify 1: Cooks, 2: Cleaners, 3: Drivers, and so on...

I thought that I came close, with the following formula, but when I entered in a different age-range, the output was no longer correct:

=INDEX($B$2:$B$26,MATCH(LARGE(IF(($E$2:$E$26,$J$2)*($D$2:$D$26>=$H$2)*($D$2:$D$26<$H$3)*($C$2:$C$26>=$I$2)*($C$2:$C$26<$I$3),$C$2:$C$26),1),$C$2:$C$26,0))

Also, I'm sure that "tie's" will become an issue, and if you might know how to solve this issue as well, that'd be great. Looking forward to hearing your thoughts and let me know if you have any questions.

Example.xlsx

In the example sheet, input data is in H2:J3 and the output formula is in G5.

Try these

Formula:

Please Login or Register  to view this content.

and copy down

Try these

Formula:

Please Login or Register  to view this content.