Forum Discussion

Holly Barber's avatar
Holly Barber
Copper Contributor
May 02, 2018

Nested IF and AND functions...

I'm really hoping someone can help me here, I've been searching other forums and I can't seem to get the formula right.... 

 

I have two columns of information that I need the function to consider and it needs to suggest the correct output.

 

Example:

If Independent and Yes; then 1

If Independent and No; then 2

If Independent and New; Then 3

If Chain and Yes; then 4

If Chain and No; then 5

If Chain and New; Then 6

 

I have the following formula: =IF(D2="Independent",IF(G2="Yes",1,IF(G2="No",2,IF(G2="New",3)))) ,IF(D2="Chain",IF(G2="Yes",4,IF(G2="No",5,IF(G2="New",6))))

 

**instead of using 1-6 I could use A-F, just in case that matters.

 

Thanks so much! 

Formulas & Functions
Need Help

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 02, 2018

    Hi Holly

     

    =MMULT({0,3},--(D2={"Independent";"Chain"}))+MMULT({1,2,3},--(G2={"Yes";"No";"New"}))
    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      May 02, 2018

      I was thinking to complicated.

       

      =(D2="Chain")*3+MATCH(G2,{"Yes";"No";"New"},0)

       

Resources