Nested if then [Solved/Closed]

Report
Posts
5
Registration date
Sunday November 9, 2014
Status
Member
Last seen
November 12, 2014
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

I would like to have the contents of b1 displayed in m2 if t2=1, the contents of b2 displayed in m2 if t2=2, the contents of c1 displayed in m2 if t2=3 etc all the way up to if t2=6.

Thanks.

5 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Chiflado,

Is the following formula possibly what you need? Place it in M2:-

=IF(T2=1,B1,IF(T2=2,B2,IF(T2=3,C1,IF(T2=4,C2,IF(T2=5,D1,IF(T2=6,D2))))))

I'm assuming that you require the first two values in the first two rows of Columns "B", "C" and "D" placed in M2 once your values in T2 are entered.

Cheers,
vcoolio.
Posts
5
Registration date
Sunday November 9, 2014
Status
Member
Last seen
November 12, 2014

Super! This is exactly what I'm looking for. I couldn't get the syntax right so I'm very thankful that you know what your doing! Thank you, thank you, thank you.
Posts
5
Registration date
Sunday November 9, 2014
Status
Member
Last seen
November 12, 2014

Is there a way to do more than 7 nested if then statements?
Posts
5
Registration date
Sunday November 9, 2014
Status
Member
Last seen
November 12, 2014

And... Is there a way to have M2 blank if T2 is blank? Right now it reads "FALSE" if nothing is in there.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Chiflado,

I believe that up to 64 IF functions can be nested in an If formula. You can probably pick up on the pattern in the formula above, so give it a try and see how it works.

To remove "False" from the cell, try this slightly amended formula:-

=IF(T2=1,B1,IF(T2=2,B2,IF(T2=3,C1,IF(T2=4,C2,IF(T2=5,D1,IF(T2=6,D2,""))))))

Regards,
vcoolio.
Posts
5
Registration date
Sunday November 9, 2014
Status
Member
Last seen
November 12, 2014

Outstanding! Thank you very much. You've saved me a TON of work.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
You're welcome Chiflado. Glad I could help.

Cheers,
vcoolio.