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

[bennybanks]

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.

 

[NARAYANK991]

Hi ,

Since you already have a formula to output the best footage for a selected Work Unit , do you want us to build on that ?

If so , can you confirm whether for a given Work Unit , will there be more than one best footage value ? If yes , that what is to be done ?

Narayan

 

[bennybanks]

Hi ,

Since you already have a formula to output the best footage for a selected Work Unit , do you want us to build on that ?

If so , can you confirm whether for a given Work Unit , will there be more than one best footage value ? If yes , that what is to be done ?

Narayan

Yes we can work on that.

By more than one best footage I am assuming you mean there are two values that would be the same thing, in that case it does not matter which is picked. I hope that answers your question.

 

[NARAYANK991]

Hi ,

In that case , can we not use a concatenation of the Work Unit and the Best Footage value , as in :

=INDEX(SpudYears , MATCH(SelectedWorkUnit & BestFootageValue , WorkUnits & BestFootages , 0))

entered as an array formula , using CTRL SHIFT ENTER.

SpudYears will be a range , SelectedWorkUnit and BestFootageValue are single cells , while WorkUnits and BestFootages are also ranges.

Narayan

 

[bennybanks]

Hi ,

In that case , can we not use a concatenation of the Work Unit and the Best Footage value , as in :

=INDEX(SpudYears , MATCH(SelectedWorkUnit & BestFootageValue , WorkUnits & BestFootages , 0))

entered as an array formula , using CTRL SHIFT ENTER.

SpudYears will be a range , SelectedWorkUnit and BestFootageValue are single cells , while WorkUnits and BestFootages are also ranges.

Narayan

I tried that and while it works on my simple sheet it does not quite work on the main sheet. I think the problem comes from the Match part of the formula. Do I pick any cell whose value I want to match?

What I am doing is going back to the data sheet and picking any cell that has the workunit which I want to match, and then picking the cell which has the MAX IF formula. The answers are not correct

So in a nut shell what I am trying to do is locate the best footage value(which has already been calculated by the MAX IF forumla) and then tell what the spud year is on a column which is to the left.

Is there a better way to do this?

 

[bennybanks]

Update!
So I looked through the index formula again and realized there was no need for the concatenation. Instead I matched the best footage value and then chose the best footage column as the array in the Match formula. Seems to work a charm. Still testing though.