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

	A	B	C
Column Headers	Project ID #	Project Name	Define QA Status
	310122	Money Service	?

<tbody>
</tbody>

Looking for "Approved" in cell C2


Worksheet 2 - Source Data

A	B	C	D
Project ID	Project Name	Status	Phase
310126	AML Card	Approved	Define
310126	AML Card	Not Started	Expand
310122	Money Service	Approved	Define
310122	Money Service	Pending	Expand
310122	Money Service	Not Started	Define Yr End

<tbody>
</tbody>

*"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

	A	B	C	D
Column Headers	Project ID #	Project Name	Define	Expand
	310122	Money Service	?	?

<tbody>
</tbody>

 

[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!