Multiple Criteria on Index/Match with Hierarchy

[perrymans]

The below formula was provided by PGC, and works great:

=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=A15,MATCH($E$2:$E$8,$E$15:$E$18,0))))

In post:
http://www.mrexcel.com/forum/showthread.php?t=573346


Now, I would like to add the ability to perform this same action/concept, but using two different criteria.

So I want to keep the hierarchical listing of importance, but lookup/match within using more than one criteria.

Example:
I have an order of superseding to apply to results of a search for Fruit.
Great
Good
Fair
Poor
(so Good supersedes, Great; Fair supersedes, Good; etc.)

Column A____Column B____Column E
Apple_______Red_________Fair
Apple_______Red_________Poor
Apple_______Green_______Great
Pear________Red_________Great
Pear________Yellow_______Great
Pear________Red_________Good
Pear________Red_________Fair


I want my verification to return all the worst Red fruit:
For Apple Red: Poor
For Pear Red: Fair


Whew! It was hard even exmplaining!

Thanks. Sean.

 

[Aladin Akyurek]

The below formula was provided by PGC, and works great:

=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=A15,MATCH($E$2:$E$8,$E$15:$E$18,0))))

In post:
http://www.mrexcel.com/forum/showthread.php?t=573346


Now, I would like to add the ability to perform this same action/concept, but using two different criteria.

So I want to keep the hierarchical listing of importance, but lookup/match within using more than one criteria.

Example:
I have an order of superseding to apply to results of a search for Fruit.
Great
Good
Fair
Poor
(so Good supersedes, Great; Fair supersedes, Good; etc.)

Column A____Column B____Column E
Apple_______Red_________Fair
Apple_______Red_________Poor
Apple_______Green_______Great
Pear________Red_________Great
Pear________Yellow_______Great
Pear________Red_________Good
Pear________Red_________Fair


I want my verification to return all the worst Red fruit:
For Apple Red: Poor
For Pear Red: Fair


Whew! It was hard even exmplaining!

Thanks. Sean.

With...

H2: Apple
I2: Red
H3: Pear
I3: Red

J2, control+shift+enter, not just enter, and copy down:

[FONT=Lucida Console]=INDEX($E$15:$E$18,MAX(IF($A$2:$A$8=H2,IF($B$2:$B$8=I2,[/FONT]
[FONT=Lucida Console]  MATCH($E$2:$E$8,$E$15:$E$18,0)))))[/FONT]