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.