Nested Formula - Need to match a range and lookup different data for the negative range result

[Prince88]

On my spreadsheet pictured I want to populate column C/ "Result" with the data in Column I/ "Action" field. I need Column B/ "No." to be looked at first and I want the data to return N if the Column I/ "Action" is N. I then need Column A/ "Ref" to be looked at and return the result in Column I/ "Action". If neither Column A/ "Ref or Column B/ "No." are listed in Column H/"NC" it can just show #N/A as I will need to add the missing reference to the list.

Thanks

A	B	C	D	E	F	G	H	I
Ref	No.	Result					NC	Action
MD182	7222						0510	N
MD408	7218						4040	N
MD408	7401						7409	N
OP382	5403						MD182	Y
OP393	0510						MD408	Y
PD189	0510						OP382	N
PD189	0510						OP393	N
PD190	0510						PD348	Y
PD190	0510						PD349	Y
PD336	0510						MD382	Y
PD348	7250						MD386	Y
PD349	7250						PD189	Y

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Ref	No.	Result					NC	Action
2	MD182	7222	Y					510	N
3	MD408	7218	Y					4040	N
4	MD408	7401	Y					7409	N
5	OP382	5403	N					MD182	Y
6	OP393	510	N					MD408	Y
7	PD189	510	N					OP382	N
8	PD189	510	N					OP393	N
9	PD190	510	N					PD348	Y
10	PD190	510	N					PD349	Y
11	PD336	510	N					MD382	Y
12	PD348	7250	Y					MD386	Y
13	PD349	7250	Y					PD189	Y
Lists

Cell Formulas
Range	Formula
C2:C13	C2	=IFNA(VLOOKUP(B2,$H$2:$I$13,2,0),VLOOKUP(A2,$H$2:$I$13,2,0))