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>

 

[63falcondude]

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

Yours is more efficient, I was just correcting mine since I misunderstood in my first response.

 

[Weazel]

sorry falcondude, the question was meant for the OP, i was just curious why they wanted to stay with the lookup formula

 

[ladyday]

Thank you!

 

[ladyday]

I know I am being a pain but I realized I need the data to pull into the first table. The formula works perfect if I put the data in the second table so I figured I would reverse the sheet names and it isn't working. I keep receiving a #NAME error message. Would you kindly take a look at the formula and tell me what I need to do pull the data into the first table.

 

[63falcondude]

I know I am being a pain but I realized I need the data to pull into the first table. The formula works perfect if I put the data in the second table so I figured I would reverse the sheet names and it isn't working. I keep receiving a #NAME error message. Would you kindly take a look at the formula and tell me what I need to do pull the data into the first table.

So you are looking to put the formula in Sheet1 now? If so, try this: (Based off of Weazel's formula)

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

Cell Formulas
Range	Formula
C2	=IF(COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,Sheet1!B2,Sheet2!C:C,"Pass"),"Y","N")

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
Sheet2

 

[Weazel]

Depending on the version of excel you are using, countifs may not be available which would be one reason you could be getting the #NAME error.

An alternative would be the sumproduct if you're not able to get FalconDudes formula to work correctly

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

Sheet1​

Worksheet Formulas Cell Formula C8 =IF(SUMPRODUCT((Sheet1!$A$2:$A$5=A8)*(Sheet1!$B$2:$B$5=B8)*(Sheet1!$C$2:$C$5="Pass")),"Y","N") <tbody> </tbody>

<tbody>
</tbody>

 

[ladyday]

Thanks but neither the sumproduct nor the if countifs are working. All return a value of N.

 

[ladyday]

Can anyone tell me what is wrong with my formula: {=IF(INDEX('QC Status'!N2:N1365,MATCH('Episode List'!D2:D1365!&'Episode List'!E2:E1365'!,'QC Status'!$A$2:$A$1365&'QC Status'!$B$2:$B$1365,0))="Pass","Y","N")} I am receiving a Name error.

 

[Aladin Akyurek]

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

<tbody>
</tbody>

Where do you want the result? In D2 of Sheet1 or in C2 of Sheet2?

 

[ladyday]

Aladin - thank you for looking into this. I was able to figure it and get it working. It ended up being:
{=INDEX('QC Status'!$N$2:$N$1365,MATCH('Episode List'!$D2&'Episode List'!$E2,'QC Status'!$A$2:$A$1365&'QC Status'!$B$2:$B$1365,0))}