asked on

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.

Christopher Rourke

Is there a specific reason you are needing to confirm the value of A in the formula w/ that AND?

SOLUTION

Thymos68

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christopher Rourke

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")

cmoerbe

ASKER

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!

cmoerbe

ASKER

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.

cmoerbe

ASKER

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.

Thymos68

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.

cmoerbe

ASKER

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.

Please stand by

Thanks

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmoerbe

ASKER

Amazing. The lookup worked perfectly.

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

Cheers!