How can I use two different inputs to determine a specific result in one cell?

Dasha Davidenko

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

David Tutwiler

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.

Dasha Davidenko

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?