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.

Column A	Column B	Column C
Series	Title	Status
Grimm	Bad Night	Fail
Queen Sugar	All Good	Fail
Chicago Fire	That Day	Pass
This is Us	Pilgrim Rick	Pass

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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
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
6
7	Series	Title	Pass/Fail
8	Grimm	Bad Night	N
9	Queen Sugar	All Good	N
10	Chicago Fire	That Day	Y
11	This is Us	Pilgrim Rick	Y

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C8 =IF(COUNTIFS($A$2:$A$5,A8,$B$2:$B$5,B8,$C$2:$C$5,"Pass"),"Y","N") C9 =IF(COUNTIFS($A$2:$A$5,A9,$B$2:$B$5,B9,$C$2:$C$5,"Pass"),"Y","N") C10 =IF(COUNTIFS($A$2:$A$5,A10,$B$2:$B$5,B10,$C$2:$C$5,"Pass"),"Y","N") C11 =IF(COUNTIFS($A$2:$A$5,A11,$B$2:$B$5,B11,$C$2:$C$5,"Pass"),"Y","N") <tbody> </tbody>

<tbody>
</tbody>

 

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