can't get lookup to work when i need it to report from a cell ref

[welshmatt]

Hi
I am trying to convert the following nested if statement to allow me to be
able to choose from more than 8 variables.

=IF(C98="A",$F$28,IF(C98="B",$F$37,IF(C98="C",$F$46,IF(C98="D",$F$55,IF(C98="E",$F$64,IF(C98="F",$F$73,IF(C98="G",$F$82,IF(C98="H",$F$91,""))))))))

this equation does work but i need to be able to also say if cell C98="I"
but due to limitations of 7 nested ifs only i can't do this. I have tried
using lookup but it doesn't allow me to create an array with the individual
cell references.

Note
C98 is blank until i enter a letter A through H.
all the references have calculations eg F37=IF(ISBLANK(B64),"",(D64/E64))

Any help would be very much appreciated
Matt

 

[Dave]

Hi,
You can concatonate IF statements using the '&' symbol, and have lots. Not
sure how many, but more than 8.
Your formula would look something like:
=IF(C98="A",$F$28,"")&IF(C98="B",$F$37,"")&IF(C98="C",$F$46,"")&IF(C98="D",$F$55,"")&IF(C98="E",$F$64,"")&IF(C98="F",$F$73,"")&IF(C98="G",$F$82,"")&IF(C98="H",$F$91,"")&IF(C98="I",Whatever,"")

Regards - Dave.

 

[welshmatt]

Thankyou very much!!!
Extremelly helpful
I'll be using this again and again

 

[Roger Govier]

Hi
Rather than lots of IF's you would be better off with a lookup table
If your lookup table was on sheet 2 in columns A and B
A F28
B F37
C F46
etc.
Then
=IF(C98="","",INDIRECT(VLOOKUP(C98,Sheet2!A:B,2,0)))

Alternatively, without a lookup table
=IF(C98="","",INDIRECT("F"&28+(CODE(C98)-65)*9))

 

[Dave]

You're welcome.
Dave.

Thankyou very much!!!
Extremelly helpful
I'll be using this again and again

 

[welshmatt]

Hi Dave
I have now entered some data into my tables to ensure that the rest of my
equations work and using the method you suggested has caused problems further
along the path with other equations.

You suggested:
cell
d78=IF(C78="A",$F$26,"")&IF(C78="B",$F$35,"")&IF(C78="C",$F$44,"")&IF(C78="D",$F$53,"")&IF(C78="E",$F$62,"")&IF(C78="F",$F$71,"") etc
I know this is only six if statements and i requested help to include more
than eight but the other sheets of the workbook contain up to 9 ifs. Cell
c78, c81 and c84 are merged cells of 3 rows 1 column.
I have used the above equation and amended slightly as follows.
cell
d79=IF(C78="A",$F$27,"")&IF(C78="B",$F$36,"")&IF(C78="C",$F$45,"")&IF(C78="D",$F$54,"")&IF(C78="E",$F$63,"")&IF(C78="F",$F$72,"")
cell
d80=IF(C78="A",$F$28,"")&IF(C78="B",$F$37,"")&IF(C78="C",$F$46,"")&IF(C78="D",$F$55,"")&IF(C78="E",$F$64,"")&IF(C78="F",$F$73,"")

Also,
cell
d81=IF(C81="A",$F$26,"")&IF(C81="B",$F$35,"")&IF(C81="C",$F$44,"")&IF(C81="D",$F$53,"")&IF(C81="E",$F$62,"")&IF(C81="F",$F$71,"")
cell
d82=IF(C81="A",$F$27,"")&IF(C81="B",$F$36,"")&IF(C81="C",$F$45,"")&IF(C81="D",$F$54,"")&IF(C81="E",$F$63,"")&IF(C81="F",$F$72,"")
cell
d83=IF(C81="A",$F$28,"")&IF(C81="B",$F$37,"")&IF(C81="C",$F$46,"")&IF(C81="D",$F$55,"")&IF(C81="E",$F$64,"")&IF(C81="F",$F$73,"")

cell
d84=IF(C84="A",$F$26,"")&IF(C84="B",$F$35,"")&IF(C84="C",$F$44,"")&IF(C84="D",$F$53,"")&IF(C84="E",$F$62,"")&IF(C84="F",$F$71,"")
cell
d85=IF(C84="A",$F$27,"")&IF(C84="B",$F$36,"")&IF(C84="C",$F$45,"")&IF(C84="D",$F$54,"")&IF(C84="E",$F$63,"")&IF(C84="F",$F$72,"")
cell
d86=IF(C84="A",$F$28,"")&IF(C84="B",$F$37,"")&IF(C84="C",$F$46,"")&IF(C84="D",$F$55,"")&IF(C84="E",$F$64,"")&IF(C84="F",$F$73,"")

I am then using the nine values obtained in column d and applying average,
sd and then perform a calculation on the mean and sd. I am now getting a
DIV/0 error in the average, sd and calculation cell.
Please help
Matt

 

[Dave]

Hi Welshmatt,
Could you please post any and all formulas, and their cell refs, that return
a DIV/0 error.
Regards - Dave.