All solutions IF Expert Solution – Excel IF Problems

Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc