Using INDEX/MATCH/MAX Multiple Criteria

I'm having trouble applying this formula to Excel, I'm trying to display the name for the highest number in the fire type.

Here is the columns and rows

Name Type 1 Type 2 Number Jake=INDEX(A2:A6,MATCH(1,((B2:B6="Fire")*(D2:D6=MAX(IF(B2:B6="Fire",D2:D6)))),0))
Jake Fire 80
Mark Water Fire 100
Thomas Fire 70
Kennzie Grass Water 40
Dan Grass Fire 60


The only problem is that this formula only counts Jake's type 1 Fire, in this Mark's Type 2 Fire has the highest count at 100. How do I incorporate the Type 2 into the formula also?

Here is a screenshot

ScreenShot2013-07-25at103423PM_zps1cb218e5.png

And the file
example.xlsx

Try in this way.

=INDEX(A2:A6,MATCH(MAX(IF(B2:C6="Fire",D2:D6)),D2:D6,0))

ARRAY formula.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

But for much bigger criteria, like this worksheet (yes I'm a Pokemon fan), the formula, which is in W2, is showing Celebi (B252) as a Water even though it is type Grass and Psychic.

It's not letting me upload the file so here is a link to download it.

http://www.mediafire.com/download/qi...tats_copy.xlsx

Hi,

Try:

=INDEX(B2:B650,MATCH(MAX(IF(D2:D650="Water",Dex!Q2:Q650)),IF(D2:D650="Water",Q2:Q650),0))

Regards