EXCEL: Find max value in a list and then return associated field 1

[DanAuber]

I'm looking for a formula to find a max value and then return an associated field

E.g. On sheet 1 I might have ABC1 in cell A1. I'd like a formula in Cell B1 that:

Goes to Sheet 2 and looks at the data - e.g.:

ABC1 11 Banana
ABC1 15 Apple
DEF3 32 Peach
DEF3 18 Pineapple
ABC1 20 Lemon

It would first only look at the ABC1 codes
Then it would find the row with the maximum value of those (in this case the line with value 20)
Then the formula would return the associated word with that row - in this case Lemon

I'm thinking there might be a cool array formula to do this - but maybe I'm asking too much ?

Dan

 

[SkipVought]

Hi,

FIRST, you need a proper table, headings and all.

THEN make your table a Structured Table via Insert > Tables > Table.

THEN your data needs to be sorted by the first column, second column DESCENDING.

Then an INDEX & MATCH formula will get you the data you seek.

Please post back with your Table Headings, so we can post a cogent solution.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DanAuber]

Hi Skip - yes Boss !

Please see the attached which I hope allows you to respond.

Regards

Dan

 

[SkipVought]

=INDEX(Table1[Fruit],MATCH(A2,Table1[Key],0),1)

Assuming your headings in row1.

Copy/Paste down through rows of data.

Posted from my iPad.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DanAuber]

Simple and Elegant - thanks !