• Status: Solved
• Priority: Low
• Security: Public
• Views: 72

# Using multiple IF AND statements in Excel formula

I am trying to compare multiple different cells from multiple columns in an Excel spreadsheet.

Here is my example data:

Type          Amount      Requirement        Status
Apples          10                  20               Not Enough Fruit
Oranges          20                  30               Enough Fruit
Banannas  5                     30               Enough Fruit
Peaches          5                          60               Enough Fruit

As you can see it works right at first, but as it goes on it starts to produce incorrect "Enough Fruit" results.

Here is the formula I use to get the data in the "Status" Column.

=IF(AND(A2="Apples",B2<20),"Not Enough Fruit", "Enough Fruit")

As you can tell this formula uses only the correct threshold for Apples, but I need this formula to contain all "Requirements" for each fruit type within the formula, so that when i drag the formula it works for all types.

I have tried many ways to append additional (IF (AND) statements to the end of the query, further nesting, etc. etc. but cannot seem to get it to work right at scale.

Any ideas on how I might be able to solve this would be immensely appreciated.
0
cmoerbe
2 Solutions

Director of OperationsCommented:
IMO you should substitute the hardcoded requirements with references.

=IF(B2<C2,"Not Enough Fruit","Enough Fruit")
2

Product Manager @ Experts ExchangeCommented:
I agree w/ Thymos68 but he forgot to account for the equal to case. I'd also recommend inverting the true/false (if for no better reason than to accentuate the positive):

=IF(B2>=C2,"Enough Fruit","Not Enough Fruit")
0

Author Commented:
Each value in A will have a different threshold. That's why I was on the path of trying to add a separate one for each line item. I can try your suggestion though and see if it works. Please stand by.

Thanks!
0

Author Commented:
I am not sure this will work. Apples has to be 20, Oranges has to be 30. There will be repeating apples / oranges / etc in the list. So each one has to be identified by type and then compared to threshold.

I need something that functions like this and accounts for each type.

=IF(AND(A2="Apples",B2<20),"Not Enough Fruit", "Enough Fruit"), =IF(AND(A3="Oranges",B3<20),"Not Enough Fruit", "Enough Fruit"), etc, etc.
0

Author Commented:
In other words it has to be apples AND 20 or more on hand. If less than 20 apples on hand then "Not enough fruit"

Then if i go to oranges, etc. I need the formula to already have the threshold stored for that type. Bananas and so on.

I hope that helps.
0

Director of OperationsCommented:
Although you can build your thresholds into a nested formula, it would be needlessly complicated.  Fine if you only have 8 items, or 12 or 20... but what if you had 100 items??  There is a much easier way to get what you're looking for by comparing one cell to another or doing a vlookup to a list of fruit to get each fruit's individual threshold number.
0

Author Commented:
It is only 4 items that I am needing to work with, but I do see your point of it not being easily expandable.  I did briefly look at using VLOOKUP for this, but being more familiar with loops than VLOOKUP i figured I would go with what seemed more familiar.

Ill try to correlate my problem to your suggested solution if you think that is a functional path forward.

Thanks
0

Commented:
I agree with Thymos68 - nested IFs will be messy.

You could store the thresholds in the workbook somewhere and reference that with VLOOKUP, or you could insert them into the formula using either VLOOKUP or CHOOSE...

``````=IF(B2<VLOOKUP(A2, {"Apples",20;"Oranges",30;"Banannas",30;"Peaches",60},2,0), "Not enough fruit", "Enough Fruit")

=IF(B2<CHOOSE(MATCH(A2, {"Apples","Oranges","Banannas","Peaches"},0), 20,30,30,60), "Not Enough Fruit", "Enough Fruit")
``````

Both formulas above do the same thing and up to you which is easier to manage.
0

Author Commented:
Amazing. The lookup worked perfectly.

Thank you both for the tips. I'll will definitely use VLOOKUP more often.

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.