# Return Value Adjacent to Two Criteria

1. ## Return Value Adjacent to Two Criteria

Capture.PNG

UPDATE 8/10/16: Picture's pretty self explanatory, see file attached. I'm struggling specifically with the presence of duplicate values that's causing the "LARGE" formulas to get stuck.

I'd like for either Lewis or Edward to show up as the person with the most oranges, then the other of the two to show up as second most (as opposed to Laura).

Thanks!

2. ## Re: Return Value Adjcent to Two Criteria

can't open the picture.

you get better help adding an excel file, without confidential information.

please also add the expected result manualy in your file.

3. ## Re: Return Value Adjcent to Two Criteria

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

4. ## Re: Return Value Adjcent to Two Criteria

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.

5. 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!

6. ## Re: Return Value Adjcent to Two Criteria

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.

7. ## Re: Return Value Adjcent to Two Criteria

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1