Return Value Adjacent to Two Criteria

You say the sheet will be "modified extensively". What does that involve, that is more that just adding rows?

Hello CatalysTim,

Try entering the following formula into cell E12 of your workbook and filling it down :

"=INDEX($A$2:$A$9,MATCH(SUMPRODUCT(MAX(($C$2:$C$9=D12)*$B$2:$B$9)),$B$2:$B$9,0))"

This formula assumes that each name will only appear once per fruit and only shows the first name that appears if there is a tie for highest # of fruit in a category.

Originally Posted by candybg

You say the sheet will be "modified extensively". What does that involve, that is more that just adding rows?

Adding new data fields, or just splicing up the dataset. If you have an tray formula in mind, that's fine. Thanks for asking!

Thanks a lot, Chenderson. Turns out I'm struggling with the very problem of when a tie is present. Would you happen to know how I could get the tied names to show up in (any) sequence, as opposed to getting stuck on the first name? See updated sample problem above.

Good news!

I managed to put together a formula that will display both top names in case of a tie and only one name if there is no tie.

This function involves the use of arrays, which means you'll need to hit (Control + Shift + Enter) instead of just Enter when editing the contents of the cells.

I hope this solution is what you're looking for!

In case anyone would like to see the function without downloading the attachment :

{=IF(MAX(($C$3:$C$8=B10)*$B$3:$B$8)=SUMPRODUCT(LARGE(($C$2:$C$8=B10)*$B$2:$B$8,2)),INDEX($A$3:$A$8,MATCH(SUMPRODUCT(MAX(($C$3:$C$8=B10)*$B$3:$B$8)),$B$3:$B$8,0))&" & "&INDEX(A1:$A$8,SMALL(IF($C$2:$C$8=B10,ROW($C$2:$C$8)-ROW(INDEX($C$2:$C$8,1,1))+1),2)),INDEX($A$3:$A$8,MATCH(SUMPRODUCT(MAX(($C$3:$C$8=B10)*$B$3:$B$8)),$B$3:$B$8,0)))}