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]

 

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

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

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

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>

Sheet1​

[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(SUMPRODUCT((Sheet1!$A$2:$A$5=A8)*(Sheet1!$B$2:$B$5=B8)*(Sheet1!$C$2:$C$5="Pass")),"Y","N")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 266"]
<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]

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