Alternative to repetitive OR in formula

Marcia · #1 07-27-2022, 07:49 AM

Hi. The code,
=IF(OR(LEFT($B$4,4)<>1016,LEFT($B$4,4)<>1021,LEFT( $B$4,4)<>1022),"A","B")
returns the correct value. Is there a shorter formula that does away with the repetitive LEFT and gives the right answer?
I tried
=IF(LEFT($B$4,4),"<>"&OR{1016,1021,1022},"A","B")
but it returns an error message.

Thank you for all the help.

ArviLaanemets · #2 07-27-2022, 11:29 AM

On fly:

Code:

=IF(ISNA(MATCH(LEFT($B$4,4),{1016,1021,1022},0)),"B","A")

Edit! Are you sure about using OR() in your formula? Or() returns True when at least one expression in it returns True - in your case to get False LEFT($B$4,4) must be equal to 1016, to 1021 and to 2022 concurrently!

Marcia · #3 07-27-2022, 03:24 PM

Thank you Arvi. The only time I used ISNA was in a code that was like IF(ISNA(VLOOKUP...) but then it simply became VLOOKUP.
I used the OR to find any number not equal to 1016, 1021 and 1022.
Again, iyaman (this is how we say thank you).

Edit: I added VALUE with the LEFT function to match with the number formatted criteria.
=IF(ISNA(MATCH(VALUE(LEFT($B$4,4)),{1016,1021,1022 },0)),"B","A")

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to skip repetitive lines to unique entries?	tbdesigns	Mail Merge	1	07-05-2020 11:04 PM
Office 365 repetitive corruption?	markg2	Office	0	10-23-2019 04:18 AM
Creating a Report Template / User Form and/or Content Controls with repetitive data throughout	53bigdog	Word VBA	1	04-05-2016 04:00 PM
automating a repetitive process	vthomeschoolmom	Excel Programming	1	02-28-2012 07:41 PM
repetitive work in word	vishalvijayan	Word	1	03-31-2011 08:30 PM