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

Options

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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    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
    edited 10/27/20
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!