Question description:
This user has given permission to use the problem statement for this
blog.
Hello - I am working on an IF function that has multiple conditions that I will need to use OR for.
Ex) IF(Cell A1 = "Apple", "FRUIT","") OR (Cell A1 = "Broccoli", "VEGETABLE", "")
Solved by O. Q. in 15 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
03/10/2017 - 05:57
Hi there, I understand that you need help with combining IF and OR functions, right?
Excelchat Expert
03/10/2017 - 05:57
Please provide a sample data we can work on.
User
03/10/2017 - 05:57
Yes, how can I help explain
User
03/10/2017 - 05:57
ok
User
03/10/2017 - 05:58
I want the formula to say "If it is an Apple, then it is a Fruit, but if it isn't an apple, it could be a banana which is also a fruit, or it could be broccoli which is vegetable"
User
03/10/2017 - 05:58
If that makes sense
Excelchat Expert
03/10/2017 - 05:59
So if column B is either Apple or banana, then it is a fruit, otherwise, if it is a Broccoli then it is a vegetable instead, right?
User
03/10/2017 - 06:00
yes, so I want one formula that will be able to diagnose all three cells
Excelchat Expert
03/10/2017 - 06:00
First, let me show you how OR is used. Please see column D for the plain OR function.
Excelchat Expert
03/10/2017 - 06:00
In column C, I'll write the complete formula you can use for all three instance.
User
03/10/2017 - 06:02
Got it, what if I wanted to make it a little more tricky
Excelchat Expert
03/10/2017 - 06:02
Column C now has the combined formula.
Excelchat Expert
03/10/2017 - 06:02
Okay, please give me some more details.
User
03/10/2017 - 06:02
so we have an equation in which it could be a Fruit, a Flower, or a Vegetable
User
03/10/2017 - 06:02
Apple = Fruit, Rose = Flower, Broccoli = Vegetable
Excelchat Expert
03/10/2017 - 06:03
You can just keep adding to the IF statement, if you MUST use IF() to solve this.
Excelchat Expert
03/10/2017 - 06:03
Otherwise, I'd recommend another approach that is easier to maintain.
User
03/10/2017 - 06:03
got it
User
03/10/2017 - 06:03
this is a very simplified version of what I'm working on, but this has been helpful!
Excelchat Expert
03/10/2017 - 06:03
The easier to maintain version is by using a reference table.
Excelchat Expert
03/10/2017 - 06:03
WOuld you like to learn more?
User
03/10/2017 - 06:05
Can you make the formula for the Apple/Rose/Broccoli option?
User
03/10/2017 - 06:05
I'm having difficulty replicating it
Excelchat Expert
03/10/2017 - 06:05
Using IF() functions or using a reference table?
User
03/10/2017 - 06:05
Using IF() functions
Excelchat Expert
03/10/2017 - 06:06
Column C has the updated IF() function.
User
03/10/2017 - 06:06
Thank you - are you able to perform a similar trick using a Reference Table?
Excelchat Expert
03/10/2017 - 06:06
Note that this solution will just get more and more difficult to maintain as you add more types.
Excelchat Expert
03/10/2017 - 06:06
Using a reference table, it'd be a lot easier.
User
03/10/2017 - 06:07
Ok, I will be adding 50+ options in here
Excelchat Expert
03/10/2017 - 06:07
I'll show you while we still have enough time.
User
03/10/2017 - 06:07
Ok, thank you
Excelchat Expert
03/10/2017 - 06:07
Column G and H is our reference table. Here is where you can add all types of information you need.
Excelchat Expert
03/10/2017 - 06:08
Then we'll use that reference table in column D.
Excelchat Expert
03/10/2017 - 06:08
Column D uses the VLOOKUP formula.
User
03/10/2017 - 06:08
Got it - I'm familiar with how to use VLOOKUP
Excelchat Expert
03/10/2017 - 06:08
It checks the value in B and looks at the reference table if it exists.
Excelchat Expert
03/10/2017 - 06:09
Oh okay, that's great then!
User
03/10/2017 - 06:09
Is it possible to use a formula there instead?
Excelchat Expert
03/10/2017 - 06:09
With this, you can simply add the Name and Type in your reference table.
Excelchat Expert
03/10/2017 - 06:09
I'm not sure I understand what you meant by that.
Excelchat Expert
03/10/2017 - 06:09
Everything we've been doing is all formulas.
User
03/10/2017 - 06:09
So if I'm trying to then calculate the value for instance...
User
03/10/2017 - 06:10
Looks like it does work!
Excelchat Expert
03/10/2017 - 06:10
Yes of course, you can do anything you want to the result of your vlookup.
Excelchat Expert
03/10/2017 - 06:10
And with this, you only have to update your reference table
Excelchat Expert
03/10/2017 - 06:10
Instead of trying to figure out how to update your IF statements.
Excelchat Expert
03/10/2017 - 06:11
I, myself, can sometimes give up on analyzing my very long IF formulas lol.
User
03/10/2017 - 06:11
Yeah, I was not excited about creating a massive series of equations...
User
03/10/2017 - 06:11
well thank you very much, this has been helpful. I think can handle it from here on out!
User
03/10/2017 - 06:11
Is there a way I can save what you've created here?
Excelchat Expert
03/10/2017 - 06:12
One moment.
Excelchat Expert
03/10/2017 - 06:12
https://docs.google.com/spreadsheets/d/17ovsRsMWZ52gK4kMMzDK2_nbnAGLpWqQU5lX03ayqZo/edit#gid=0
Excelchat Expert
03/10/2017 - 06:12
You can download the sheet by going to the link above, clicking FILE > DOWNLOAD AS > Microsoft Excel (.xlsx)
Excelchat Expert
03/10/2017 - 06:12
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
User
03/10/2017 - 06:12
Perfect, thank you
Excelchat Expert
03/10/2017 - 06:12
Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
03/10/2017 - 06:12
and yes very much so - thanks again!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
