How can I use two different inputs to determine a specific result in one cell?
Hi there  I'm not sure what is the best formula to use & appreciate any help! I have a 9 different scenarios that can return 4 different results. This is something like a matrix translated into the possible combinations.
The matrix / combinations are in a separate sheet but can also be built into the formula directly, if that is easier.
Combinations:
Importance: 1; Feasibility: 2; result = Rejected
Importance: 2; Feasibility: 1; result = Backlog
Importance: 3; Feasibility: 1; result = Priority
... and so on for a total of 9.
I need to consider what is returned in the Importance and Feasibility columns, not just the total sum. I tried playing around with IF, AND, OR statements  but it feels clunky. Any other suggestions?
Thanks again!
Dasha
Answers

I think you're going to need a long nested IF statement. Something like:
=IF(AND(Importance@row = 1, Feasibility@row = 2), "Rejected", IF(AND(Importance@row = 2, Feasibility = 1), "Backlog", IF(etc. etc.
I think you were on the right track. Might be clunky, but the hardest part is just making sure you have the right amount of close parentheses when you're done.

Thanks! I have tried that, the thing with that it only returns the first true value. Here's the formula I was using:
=IF(AND(Importance@row = 1, Feasibility@row = 1), "Rejected", IF(AND(Importance@row = 2, Feasibility@row = 1), "Approved  Backlog", IF(AND(Importance@row = 1, Feasibility@row = 2), "Approved  Backlog", IF(AND(Importance@row = 3, Feasibility@row = 1), "Approved  Priority 2"))))
It is also going to get long  do you think a vlookup from a separate sheet may work better?
Help Article Resources
Categories
Check out the Formula Handbook template!