v-lookup, match, index combo to return 1 of 3 diff types of statuses

[dcotex]

I'm looking for 1 of the 3 types of statuses (Not Started, Pending, Approved) according to the project ID, project Name, and the current phase Define (in header) from worksheet 1 and value to be returned in worksheet 2



Worksheet 1 - formula in cell C2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Column Headers[/TD]
[TD]Project ID #[/TD]
[TD]Project Name[/TD]
[TD]Define QA Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]310122[/TD]
[TD]Money Service[/TD]
[TD="align: center"]?[/TD]
[/TR]
</tbody>[/TABLE]
Looking for "Approved" in cell C2


Worksheet 2 - Source Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]Project ID[/TD]
[TD]Project Name[/TD]
[TD]Status[/TD]
[TD]Phase[/TD]
[/TR]
[TR]
[TD]310126[/TD]
[TD]AML Card[/TD]
[TD]Approved[/TD]
[TD]Define[/TD]
[/TR]
[TR]
[TD]310126[/TD]
[TD]AML Card[/TD]
[TD]Not Started[/TD]
[TD]Expand[/TD]
[/TR]
[TR]
[TD]310122[/TD]
[TD]Money Service[/TD]
[TD]Approved[/TD]
[TD]Define[/TD]
[/TR]
[TR]
[TD]310122[/TD]
[TD]Money Service[/TD]
[TD]Pending[/TD]
[TD]Expand[/TD]
[/TR]
[TR]
[TD]310122[/TD]
[TD]Money Service[/TD]
[TD]Not Started[/TD]
[TD]Define Yr End[/TD]
[/TR]
</tbody>[/TABLE]

*"Define Yr" End is not the same as "Define", looking for status of "Define" only.*


So, to reiterate, in worksheet 1 cell C2, looking to return the "Approved" Status for Define for Project 310122 Money Service.


Appreciate any assistance!



-DCotex

 

[Jonmo1]

Try this for a start

=INDEX('Worksheet 2'!$C$2:$C$6,MATCH(1,INDEX(('Worksheet 2'!$A$2:$A$6=A2)*('Worksheet 2'!$B$2:$B$6=B2)*('Worksheet 2'!$D$2:$D$6="Define"),0),0))

Will need more detail on how you get the 'Phase' out of the header in C1, for now "Define" is hard coded in the formula.

 

[Eric W]

A similar formula:

=LOOKUP(1,1/((Sheet2!$A$2:$A$6=A2)*(Sheet2!$D$2:$D$6=LEFT($C$1,SEARCH(" QA",$C$1&" QA")-1))),Sheet2!$C$2:$C$6)

I assumed I didn't need to look for both A2 and B2, since they appear to have a 1-1 relationship. I also was unsure how you wanted to get "Define" from the C1 cell, I used the LEFT(SEARCH construct to find whatever's on the left of QA.

 

[dcotex]

Hmm, you both may be right, what if I edited the column headers accordingly, it will ultimately include other phase types, for which I will need to return the statuses of each, I added another column for insight. So ultimately I am looking for "Approved" in cell C2 and "Pending" in cell D2 in worksheet 1...


Worksheet 1 - edited
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]Column Headers[/TD]
[TD]Project ID #[/TD]
[TD]Project Name[/TD]
[TD]Define[/TD]
[TD]Expand[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]310122[/TD]
[TD]Money Service[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Great, then you can just put the cell reference there instead of the hard coded "define"

=INDEX('Worksheet 2'!$C$2:$C$6,MATCH(1,INDEX(('Worksheet 2'!$A$2:$A$6=A2)*('Worksheet 2'!$B$2:$B$6=B2)*('Worksheet 2'!$D$2:$D$6=C$1),0),0))

 

[Eric W]

A similar adjustment to my formula:

=LOOKUP(1,1/((Sheet2!$A$2:$A$6=$A2)*(Sheet2!$D$2:$D$6=C$1)),Sheet2!$C$2:$C$6)

place in C2 and drag down and to the right as needed.

 

[dcotex]

Boom. thank you Both!