Help. I'm stumped on this multiple cell IF, ISNUMBER, MATCH, IFNA, OR, VLOOKUP formula. I almost got it!

[hunting_fishing]

E4:
=IF(F4="Yes","No",IF(OR((ISNUMBER(MATCH(A4,'Remington 700'!$N$7:$N$401,0))),(ISNUMBER(MATCH(A4,'Bighorn TL3'!$N$7:$N$401,0)))),"Yes","No"))

F4:
=IFNA(IF(OR(IFNA(((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=1),((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=1)),IFNA(((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=1),((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=1))),"Yes","No"),"")

Stumped.. need some help
Need help with the formula in E4, I almost have it.

E4 turns to Yes if A4 (which is always "1" its a Routing control number) ever appears in 'Remington 700'!$N$7:$N$401 or 'Bighorn TL3'!$N$7:$N$401 or to NO if F4="Yes"

F4 turns Yes if a "1" is ever placed in the 2nd row next to A4(which is always "1" its a Routing control number), Note: Putting a 1 in F4 indicates that that inventory number has been used

Everything is working as the formula states, but I want more:
What I want:
A4 is a reusable item, so I want E4 to return to Yes IF at ANYTIME A4 (which is always "1" its a Routing control number) AGAIN ever appears later down the columns of 'Remington 700'!$N$7:$N$401 or 'Bighorn TL3'!$N$7:$N$401. Sort of overriding the Yes in F4

Thanks for looking,
Seve

 

[hunting_fishing]

Anything?

 

[hunting_fishing]

=IFNA(IF(AND(IFNA(((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=1),((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=1)),IFNA(((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=1),((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=1)),IFNA(((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=0),((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=0)),IFNA(((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=0),((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=0))),"Yes",IF(AND(IFNA(((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=1),((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=1)),IFNA(((VLOOKUP(A4,'Bighorn TL3'!$N$7:$O$400,2,FALSE))=1),((VLOOKUP(A4,'Remington 700'!$N$7:$O$400,2,FALSE))=1))),"No","Yes")),"")

I have this in E4 now. It is working better than originally across multiple sheets in the book, but I still do not have the desired effect.
What I want:
A4 is a reusable item, so I want E4 to return to Yes IF at ANYTIME A4 (which is always "1" its a Routing control number) AGAIN ever appears later down the columns of 'Remington 700'!$N$7:$N$401 or 'Bighorn TL3'!$N$7:$N$401 with nothing is the 2nd column next to it(blank). Sort of overriding the Yes in F4

 

[hunting_fishing]

UP