worksheet function to return index of largest value

[bmurphy]

Hello Excel'ers,

I have two columns of worksheet data.
column A contains integer values, in any order, some values repeated
column B contains real values

I want to return the index in B of the largest value in B that has a
given value in A

A B
1 1.1
2 2.1
3 3.1
1 4.1
2 5.1
3 4.1
1 3.1
2 2.1
3 11.1

For instance, for 1 return 4
For instance, for 2 return 5
For instance, for 3 return 9

Is this possible with a single worksheet function? I've tried mixing
MAX and MATCH but can't get it to work.

I should add that if there is more than one row with the right answer,
the index to any of them would be ok.

Thanks,

Brian Murphy
Austin, TX

 

[Pete_UK]

Assuming your data occupies A1:B9, and with 1, 2, 3 in D13, put this
array* formula in E1 and copy down to E3:

=MATCH(MAX(IF($A$1:$A$9=$D1,$B$1:$B$9,0)),$B$1:$B$9,0)

*As this is an array formula, then once you have typed it in (or
subsequently edit it), you must commit it using CTRL-SHIFT and ENTER
(CSE) instead of just ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - you must not type these yourself.

Hope this helps.

Pete