Too many IFs-when trying to apply my IF function ?

**mister tom** · 11-22-2009, 02:44 PM

when trying to apply my IF function to excel i keep getting the following error:

"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

here is the formula im trying to enter:

=IF(AND(O174>=2000,O174<2050),VLOOKUP(TRUE,Sheet6!O73:AA783,10,FALSE),IF(AND(O174>=2050,O174<2100),VLOOKUP(TRUE,Sheet6!AC74:AO784,10,FALSE),IF(AND(O174>=2100,O174<2150),VLOOKUP(TRUE,Sheet6!AQ56:BC766,10,FALSE),IF(AND(O174>=2150,O174<2200),VLOOKUP(TRUE,Sheet6!BE74:BQ784,10,FALSE),IF(AND(O174>=2200,O174<2250),VLOOKUP(TRUE,Sheet6!BS74:CE784,10,FALSE),IF(AND(O174>=2250,O174<2300),VLOOKUP(TRUE,Sheet6!CG74:CS784,10,FALSE),IF(AND(O174>=2350,O174<2400),VLOOKUP(TRUE,Sheet6!DI74:DU784,10,FALSE),IF(AND(O174>=2400,O174<2450),VLOOKUP(TRUE,Sheet6!DW74:EI784,10,FALSE),IF(AND(O174>=2450,O174<2500),VLOOKUP(TRUE,Sheet6!EK74:EW784,10,FALSE),IF(AND(O174>=2500,O174<4000),VLOOKUP(TRUE,Sheet6!EY74:FK784,10,FALSE),0))))))))))

i followed a help file from a website for IF and and functions to do 2 variables, why does it not work with the 9 variables i have above?

**martindwilson** · 11-22-2009, 03:12 PM

7 nested ifs is your limit pre excel 2007

**protonLeah** · 11-22-2009, 03:31 PM

Please describe the layout and data on sheet6 and why the lookup value is always TRUE.

**daddylonglegs** · 11-22-2009, 03:40 PM

You could probably use CHOOSE function to give you more options. The only thing that differs for each VLOOKUP appears to be the lookup range so you can use a CHOOSE(MATCH construction to supply the right lookup range based on the value in O174.

It looks like you have a VLOOKUP missing, though, as you haven't covered the range from 2300 to 2350. I put an "x" in the formula where the missing range should be....

=IF(AND(O174>=2000,O174<4000),VLOOKUP(TRUE,CHOOSE(MATCH(O174-2000,{0,50,100,150,200,250,300,350,400,450,500}),Sheet6!O73:AA783,Sheet6!AC74:AO784, Sheet6!AQ56:BC766,Sheet6!BE74:BQ784,Sheet6!BS74:CE784,Sheet6!CG74:CS784,"X",Sheet6!DI74:DU784,Sheet6!DW74:EI784,Sheet6!EK74:EW784,Sheet6!EY74:FK784),10,FALSE),0)