Peform a vlookup using 3 conditions (non-numerical)...

[ladyday]

Hi,

I need assistance performing a vlook up in a table where I would like the formula to look at the data in Sheet 1, Columns A & B and if they match the information in Sheet 2, Columns A & B (same data) go back to Sheet 1 and look at the status of Column C and return a value of Y for pass or N for fail in Column C. I hope I said that correctly.

[TABLE="width: 266"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Series[/TD]
[TD]Title[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Grimm[/TD]
[TD]Bad Night[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Queen Sugar[/TD]
[TD]All Good[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]Chicago Fire[/TD]
[TD]That Day[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]This is Us[/TD]
[TD]Pilgrim Rick[/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

You are in need of the Array form of INDEX(MATCH())

Checkout segment nine of Mike's video at https://www.youtube.com/watch?v=9m3TN3_5AOo

 

[63falcondude]

Try this (note that you will have to adjust the ranges in the formula to match your data)

Excel 2010
	A	B	C
1	Column A	Column B	Column C
2	Series	Title	Status
3	Grimm	Bad Night	Fail
4	Queen Sugar	All Good	Fail
5	Chicago Fire	That Day	Pass
6	This is Us	Pilgrim Rick	Pass
Sheet1

Excel 2010
	A	B	C
1	Column A	Column B	Formula
2	Grimm	Bad Night	Fail
3	Chicago Fire	That Day	Pass
4	Series	Title	Status
5	Queen Sugar	All Good	Fail
6	This is Us	Pilgrim Rick	Pass
Sheet2

Cell Formulas
Range	Formula
C2	{=INDEX(Sheet1!$C$2:$C$6,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$6&Sheet1!$B$2:$B$6,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Weazel]

If I'm understanding correctly maybe...

Excel 2013

	A	B	C
N
N
Y
Y

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Series[/TD]
[TD="bgcolor: #FAFAFA"]Title[/TD]
[TD="bgcolor: #FAFAFA"]Status[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Grimm[/TD]
[TD="bgcolor: #FAFAFA"]Bad Night[/TD]
[TD="bgcolor: #FAFAFA"]Fail[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Queen Sugar[/TD]
[TD="bgcolor: #FAFAFA"]All Good[/TD]
[TD="bgcolor: #FAFAFA"]Fail[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Chicago Fire[/TD]
[TD="bgcolor: #FAFAFA"]That Day[/TD]
[TD="bgcolor: #FAFAFA"]Pass[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]This is Us[/TD]
[TD="bgcolor: #FAFAFA"]Pilgrim Rick[/TD]
[TD="bgcolor: #FAFAFA"]Pass[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Series[/TD]
[TD="bgcolor: #FAFAFA"]Title[/TD]
[TD="bgcolor: #FAFAFA"]Pass/Fail[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Grimm[/TD]
[TD="bgcolor: #FAFAFA"]Bad Night[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Queen Sugar[/TD]
[TD="bgcolor: #FAFAFA"]All Good[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Chicago Fire[/TD]
[TD="bgcolor: #FAFAFA"]That Day[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]This is Us[/TD]
[TD="bgcolor: #FAFAFA"]Pilgrim Rick[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A$5,A8,$B$2:$B$5,B8,$C$2:$C$5,"Pass"),"Y","N")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A$5,A9,$B$2:$B$5,B9,$C$2:$C$5,"Pass"),"Y","N")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A$5,A10,$B$2:$B$5,B10,$C$2:$C$5,"Pass"),"Y","N")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A$5,A11,$B$2:$B$5,B11,$C$2:$C$5,"Pass"),"Y","N")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Wow. I just realized that I took Column A, B, and C as the headers...

 

[ladyday]

This actually worked fine between the two sheets. How can I add the pass = Y component to this array?

 

[ladyday]

Sorry it is this formulat that worked fine between the two sheets. How can I add the pass = Y component to this array?

 

[63falcondude]

Excel 2010
	A	B	C
1	Series	Title	Status
2	Grimm	Bad Night	Fail
3	Queen Sugar	All Good	Fail
4	Chicago Fire	That Day	Pass
5	This is Us	Pilgrim Rick	Pass
Sheet1

Excel 2010
	A	B	C
1	Series	Title	Pass?
2	Grimm	Bad Night	N
3	Queen Sugar	All Good	N
4	Chicago Fire	That Day	Y
5	This is Us	Pilgrim Rick	Y
Sheet2

Cell Formulas
Range	Formula
C2	{=IF(INDEX(Sheet1!$C$2:$C$6,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$6&Sheet1!$B$2:$B$6,0))="Pass","Y","N")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Weazel]

why would you want to use a large array formula for this?

 

[ladyday]

why would you want to use a large array formula for this?

Because I am comparing over 2K lines of data with more on the way and I would love to automate the process as this is only 5% of my work.