IF AND not working as expected. See expected results

[Mllam]

Test.xlsx
	A	B	C	D	E	F	G	H
1	Sku	Competitor Sku	Cross 1	Disti 1	Cross 2	Disti 2	IF AND	Expected Results
2	111	11x	11x	Z	11x	X	Z and X	Z and X
3	222	22x	22x	Z	#N/A	#N/A	#N/A	Z
4	333	33x	33x	Z	33x	X	Z and X	Z and X
5	444	44x	#N/A	#N/A	#N/A	#N/A	#N/A	No
6	555	55x	55x	Z	55x	X	Z and X	Z and X
7
8	Disti 1 Sku		Disti 2 Sku
9	11x		11x
10	22x		33x
11	33x		55x
12	55x
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=VLOOKUP(B2,$A$9:$A$12,1,FALSE)
D2:D6	D2	=IF(C2<>"#N/A","Z","#N/A")
E2:E6	E2	=VLOOKUP(B2,$C$9:$C$11,1,FALSE)
F2:F6	F2	=IF(E2<>"#N/A","X","#N/A")
G2:G6	G2	=IF(AND(D2<>"#N/A",F2<>"#N/A"),"Z and X",IF(AND(D2="#N/A",F2="#N/A"),"No",IF(AND(D2<>"#N/A",F2="#N/A"),D2,IF(AND(D2="#N/A",F2<>"#N/A"),F2,"No"))))

 

[shift-del]

Hi

A simplified version.

Mappe7
	A	B	C	D	E
1	Sku	Competitor Sku	Disti 1	Disti 2	Result
2	111	11x	Z	X	Z and X
3	222	22x	Z		Z
4	333	33x	Z	X	Z and X
5	444	44x
6	555	55x	Z	X	Z and X
7
8	Disti 1 Sku		Disti 2 Sku
9	11x		11x
10	22x		33x
11	33x		55x
12	55x
Tabelle1 (2)

Cell Formulas
Range	Formula
C2:C6	C2	=IF(COUNTIFS($A$9:$A$12,B2),"Z","")
D2:D6	D2	=IF(COUNTIFS($C$9:$C$11,B2),"X","")
E2:E6	E2	=TEXTJOIN(" and ",TRUE,C2:D2)