Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-27-2022, 07:49 AM
Marcia's Avatar
Marcia Marcia is offline Alternative to repetitive OR in formula Windows 10 Alternative to repetitive OR in formula Office 2019
Expert
Alternative to repetitive OR in formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Alternative to repetitive OR in formula


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.
Reply With Quote
  #2  
Old 07-27-2022, 11:29 AM
ArviLaanemets ArviLaanemets is offline Alternative to repetitive OR in formula Windows 8 Alternative to repetitive OR in formula Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #3  
Old 07-27-2022, 03:24 PM
Marcia's Avatar
Marcia Marcia is offline Alternative to repetitive OR in formula Windows 10 Alternative to repetitive OR in formula Office 2019
Expert
Alternative to repetitive OR in formula
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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")
Reply With Quote
Reply

Thread Tools
Display Modes


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
Alternative to repetitive OR in formula repetitive work in word vishalvijayan Word 1 03-31-2011 08:30 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft