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

Bennybanks

Bennybanks

New member
Joined
Feb 3, 2015
Messages
1
Reaction score
0
Points
0
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
2013PeterPassion1000
2014JohnIntegrity3000
2013
JohnIntegrity2500
2015MaryRespect3300
2014JohnPassion2300



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:
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))
 
You must log in or register to reply here.
Back
Top