The 7 nested If() function Limitation. Is there anouther way?

D

DMB

I need to be able to check for more alternatives. These are Structural
Hardware and my if statement chooses a holdown type base on a cell and the
capacity of the hardware.

=IF(C69=0, 0,
IF(C69<500, "N.R.",
IF( C69<3610, "PHD2",
IF( C69<4685, "PHD5",
IF( C69<5860, "PHD6",
IF( C69<8325, "HDQ8",
IF( C69<9615, "HHDQ11",
IF( C69<12350,
"HHDQ14", "VERIFY"))))))))

I need to do more checks after the 12350 lb capacity. There could be a dozen
or more needed.

Thankyou DMB
 
N

Niek Otten

Look in HELP for the VLOOKUP() function.
Easier to read, much easier to maintain.
Post again if you have difficulties using it.
 
D

damorrison

you may need to use vlookup, probably the hardest function to learn but
once you got it you will never forget it!!
 
D

DMB

I looked at the vlookup function and I am not sure how to set it up for these
conditions.

I am also trying the lookup function but I can't create the right condition!

=LOOKUP(C18<,C1:C4,B1:B4)

c18 = 3500

C D
1500 PHD1
2500 PHD2
3500 PHD3
4500 PHD4
 
N

Niek Otten

Don't use LOOKUP, use VLOOKUP and follow the advice in the link I included
in my last answer
 
D

DMB

I am reading it now. in the formula for the sample spreadsheet they refer to
the lookup table as GradeList? The excel tab containing the data is called
Grades. How is this accomplished?

=VLOOKUP(B4,GradeList,2)
 
P

Pete

"GradeList" is the name of the table that the Vlookup formula searches
through, so you have to tell Excel how to recognise this name. Assuming
your table of values covers C2 to D20, you should highlight these cells
then Insert | Name | Define. You should see your range shown in the
"Refers to" box at the bottom, and you just type the name you want then
click OK.

Alternatively, your formula could be written as:

=VLOOKUP(B4,$C$2:$D$20,2)

Hope this helps.

Pete
 
D

DMB

Hey thanks alot for the help. Vlookup really cleans up my spreadsheet and
also makes it user friendly for the folks at the office.
 
D

daddylonglegs

Lookup is, in fact, a better alternative.

Your formula will be shorter and quicker

=LOOKUP(B4,$C$2:$D$20)
 
Top