Double IF statement and Vlookup

[ldashev]

Need some help with a formula and I'm going to try to formulate what I'm trying to achieve and write down the logic below in steps:
1) Take value in TABLE 1 and Lookup for matching value in TABLE 2
2) If value is found then go 5 fields across (to the right) and bring back the value (value are either "blank", "Yes" or "No")
3) When value is blank record in the field as " ", when value is "No" record it as "N", when value is "Yes" go 2 cells over and bring back the value in the cell.

so far I have the formula covering steps 1 and 2, but struggling to get step 3, here's the formula below:

=IF(ISBLANK(VLOOKUP(D2,'QA Sheet'!$A$2:$AM$339,5,FALSE))," ",VLOOKUP(D2,'QA Sheet'!$A$2:$AM$339,5,FALSE))

TABLE 1 - is where the output goes
TABLE 2 - contains all of the data

 

[Eric W]

If I'm recreating your spreadsheet properly, then this should work:

	A	B	C	D	E	F	G	H	I
N
	A
Yes	B
No	C
Yes	D
	E
No	F
Yes	G
No	H

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

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

QA Sheet​

[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"]I1[/TH]
[TD="align: left"]=IFERROR(CHOOSE(MATCH(VLOOKUP(D2,'QA Sheet'!$A$2:$AM$339,5,FALSE),{" ","No","Yes"},0)," ","N",VLOOKUP(D2,'QA Sheet'!$A$2:$AM$339,7,FALSE)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ldashev]

Thank you! That worked to perfection

 

[Eric W]

Glad to help!