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

hunting_fishing

New Member
Joined
Aug 2, 2018
Messages
4
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
=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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top