Index Match challenge in F18

Hi ...

How do I pick out the largest number i the array B2:I9, and in return get the corresponding name in A2:A9, and month from column title?
I have tried with Index;Match with multiple criteria, Rank, Large, countif and countifs, but I can't seem to get it to work.
Index;Match with one column works fine, but when I want to index the whole arry, I get an #N/A

How can this be done?

Any answer is much appreciated.

Edit: Removed.

This array* formula will return the name of the person with the largest number in the range B2:L9:

=INDEX(A:A,MIN(IF($B$2:$L$9=LARGE($B$2:$L$9,1),ROW($B$2:$B$9))))

and this one will return the month:

=INDEX(1:1,MIN(IF($B$2:$L$9=LARGE($B$2:$L$9,1),COLUMN($B$1:$L$1))))

*NOTE that as both of these are array formulae, they need to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

You can, of course, use MAX instead of the LARGE function.

Hope this helps.

Pete

63falcondude had the beste solution, because there was no need to pin point out a certain column first.
Hereby implemented.
AGGREGATE ... seems like av versatile function. Learning every day now!



Thanks for the tips, folks.

Funny thing though ...
In "Superheros Trust Index.xlsx" both suggestions works fine, but in the workbook I'm working with I get a #REF?!?!
CSE was used. Didn't fix it.

A #REF error indicates that the cell or range references are not valid, so check these out in the formulae.

Hope this helps.

Pete

Isn't this correct, huh?
Or am I blind?
Attachment 644798

The attachment is invalid. Don't try to use the Paperclip icon, as it doesn't work on this forum - instead, you should use Go Advanced then scroll down and click on Manage Attachments, then follow the onscreen prompts.

Hope this helps.

Pete

ahh ...

Here it is ...