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

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

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.

2. 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
•