find location of maximum and get the category type

[Pablo]

I know this is really easy in Access, but unfortunately I have to work on
Excel...

I need to do a query that will search for the maximum value and give me the
type. Here's the example:

A B
1 Categories Results
2 Type 1 10
4 Type 2 30
5 Type 3 20


In this case, the maximum value for this array is 30. I want my query to
find the 30 and give me "Type 2" as the result.

I hope that this can be done with built-in formulas, rather than adding a
v-basic macro... Thank you!

Pablo

 

[Sean Timmons]

If you can swap those columns, then you could do a VLOOKUP

=VLOOKUP(MAX(A:A),A:B,2,FALSE)

would get it.

 

[Rick Rothstein]

I think this should work...

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

 

[RagDyer]

Try this:

=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))

 

[ShaneDevenshire]

Hi,

Here is a minor simplificatins

=INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4))

and if you range named A2:A4 A and B2:B4 B

=INDEX(A,MATCH(MAX(B),B,0))