Inserting 10 or more IF in one Cell.

[countryfan_nt]

Hello friends,

I have a VLOOKUP formula in cell X1. I am trying to insert all the below 10 IF statements in it. I have three problems:

A) Excel does not allow inserting that many conditions in one cell.

B) Don't want to use VBA.

C) VLOOKUP doesn't work, since the table array contains formulas. I can't have the table arrays change from 1s to 2s, when copying & pasting X1 to X2.

1. IF(A1=1,C1*D1)
2. IF(A1=2,E1*F1)
3. IF(A1=3,G1*H1)
4. IF(A1=4,I1*J1)
5. IF(A1=5,K1*L1)
6. IF(A1=6,M1*N1)
7. IF(A1=7,O1*P1)
8. IF(A1=8,Q1*R1)
9. IF(A1=9,S1*T1)
10. If all false then "NO".


All the best & Thank you so much,
Nawaf

 

[Richard Schollar]

Hi Nawaf

You could use CHOOSE:

=CHOOSE(IF(OR(A1={1,2,3,4,5,6,7,8,9}),A1,10),C1*D1,E1*F1,G1*H1,I1*J1,K1*L1,M1*N1,O1*P1,Q1*R1,S1*T1,"NO")

Or given the structure of your data you could additionally use some kind of OFFSET.

 

[barry houdini]

...or INDEX, i.e.

=IF(AND(A1>=1,A1<=9),INDEX(A1:T1,A1*2+1)*INDEX(A1:T1,A1*2+2),"No")

 

[konew1]

Hi
Try this
=IF(A1<10,CHOOSE(A1,C1*D1,E1*F1,G1*H1,I1*J1,K1*L1,M1*N1,O1*P1,Q1*R1,S1*T1),"No")

 

[xenou]

here's my take on RichardSchollar's idea with Offset included:

=IF(OR(A1={1,2,3,4,5,6,7,8,9}),(OFFSET(A1,0,A1*2)*OFFSET(A1,0,A1*2+1)),"NO")

Edit: oops, I deleted a formula that was exactly like another post...

 

[RalphA]

This is my third time in this board that I explain the following:

The creators of BASIC decided that allowing up to seven nested functions, such as the IF function (one main function, plus seven nested ones), for a total of eight, was a reasonble compromise for almost all cases.

The development of BASIC into QuickBASIC, then into VisualBASIC, and, now, into Visual Basic for Applications, has not changed that compromise, and it is the same for a formula.

However, up to thirty strings may be concatenated. So, this allows us to use a huge number of IFs, but we must use the seventh nested one in each group to allow the result to be meaningful, so, if the first group has not found a true IF, the last IF of the group must produce a "", or blank entry, to be concatenated with the following group(s).

Here is an example, using your data, in which I used eight IF functions in one part of the formula (one main IF, with seven nested IFs), and one, extra IF, joined by the Excel concatenation symbol, &. Please realize that, if A1 equals any of 1 through 9, and one of the corresponding two multiplied cells is empty, the result you will see will be 0. Only if A1 does not have any of 1 through 9 will you see NO. Cheers!

=IF(A1=1,C1*D1,IF(A1=2,E1*F1,IF(A1=3,G1*H1,IF(A1=4,I1*J1,IF(A1=5,K1*L1,IF(A1=6,M1*N1,IF(A1=7,O1*P1,IF(A1=8,Q1*R1,"")))))))) & IF(A1=9,S1*T1,"NO")

 

[barry houdini]

....or perhaps a single OFFSET...

=IF(OR(A1={1,2,3,4,5,6,7,8,9}),PRODUCT(OFFSET(A1,,A1*2,,2)),"NO")

 

[Norie]

Ralph

Are you sure there's a 7 nested If limit in VBA?

 

[RalphA]

Norie:

No. I am not into VBA, but, I quite believe that will be the case, based on my experience with Excel formulas. Perhaps you can give it a try, or, if you lead the way, I will do so, but, I will need help!

 

[Norie]

Ralph

I did try a simple test.

Wasn't particularly scientific but the code compiled and seemed to run OK.

But then again it really didn't do much, if anything.

Anyways in VBA if I was getting to that level of nesting I think I'd be looking at a bit of restructuring. eg Select Case

And as Richard, Barry, Alexander et al have illustrated there are formula solutions to overcome the limitation.