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

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

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

would get it.



"Pablo" wrote:

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

I think this should work...

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

--
Rick (MVP - Excel)


"Pablo" wrote in message
...
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

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pablo" wrote in message
...
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

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

--
Thanks,
Shane Devenshire


"Pablo" wrote:

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