+ Reply to Thread
Results 1 to 7 of 7

Return Value Adjacent to Two Criteria

  1. #1
    Registered User
    Join Date
    07-21-2015
    Location
    USA
    MS-Off Ver
    Excel 2007 (Windows & Mac)
    Posts
    6

    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!
    Attached Files Attached Files
    Last edited by CatalysTim; 08-10-2016 at 10:54 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    08-25-2015
    Location
    Near Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    150

    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. #4
    Registered User
    Join Date
    02-15-2016
    Location
    United States
    MS-Off Ver
    2010
    Posts
    33

    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. #5
    Registered User
    Join Date
    07-21-2015
    Location
    USA
    MS-Off Ver
    Excel 2007 (Windows & Mac)
    Posts
    6
    Quote Originally Posted by candybg View Post
    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. #6
    Registered User
    Join Date
    07-21-2015
    Location
    USA
    MS-Off Ver
    Excel 2007 (Windows & Mac)
    Posts
    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. #7
    Registered User
    Join Date
    02-15-2016
    Location
    United States
    MS-Off Ver
    2010
    Posts
    33

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return Min Value With Criteria
    By zmster2033 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-09-2016, 12:06 PM
  2. Replies: 1
    Last Post: 10-01-2014, 04:50 PM
  3. Index Match using two criteria to return on of criteria values
    By RogueLeader in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 09:50 AM
  4. Return dates that match a single criteria (a countif criteria)
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2014, 12:43 PM
  5. Replies: 3
    Last Post: 12-13-2013, 06:23 AM
  6. Lookup two criteria and return a third criteria as result. Aaaargh!
    By dearthofjoy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-08-2013, 12:13 PM
  7. [SOLVED] Excel 2007 : Return value if four criteria are met
    By CatherineCarey in forum Excel General
    Replies: 4
    Last Post: 04-26-2012, 10:16 AM

Bookmarks

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