Results 1 to 2 of 2

Thread: Formula to Return Values From another column after meeting conditions on two

  1. #1

    Formula to Return Values From another column after meeting conditions on two



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,
    This problem has got me a beat.
    What I want is a way to perform conditional statements on two columns. When both conditions are true, I want to return the value from a third column that is in the same row as where these conditions were met.
    Example
    Spud Year Name Work unit Best footage
    2013 Peter Passion 1000
    2014 John Integrity 3000
    2013 John Integrity 2500
    2015 Mary Respect 3300
    2014 John Passion 2300


    There is a huge database like this on one sheet and on another sheet I have to make summaries for each work unit. I want a formula that would check the best footage done by a work unit, then return the name and the spud year. So If I want to see the best footage for Integrity the answer should come off as

    Best Footage Name Spud year
    3000 John 2014

    I already used a MAX IF formula to get the best footage, but I am struggling with how to return the other data. I hope this explanation is good enough, I am at wits end.
    Last edited by Bennybanks; 2015-02-03 at 08:14 AM.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Use the following array formulae

    =MAX(IF(Sheet3!$C$1:$C$20=$A2,Sheet3!$D$1:$D$20))

    =INDEX(Sheet3!$B$1:$B$20,MATCH(ROW(Sheet3!$A$1:$A$20),(Sheet3!$C$1:$C$20=$A2)*($B2=Sheet3!$D$1:$D$20),0))

    =INDEX(Sheet3!$A$1:$A$20,MATCH(ROW(Sheet3!$A$1:$A$20),(Sheet3!$C$1:$C$20=$A2)*($B2=Sheet3!$D$1:$D$20),0))

Posting Permissions

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