Too many IFs and VLOOKUPs????

[KrisB_bacon]

A friend of mine constructed this function to lookup data on School
Classes depending on the year. As a joke he input Bugger as the last
message. He can take it out on his computer, but if I try to remove
Bugger, it says the formula has an error. Are there too many
functions??


=IF(B2=2002,IF(C2="No","",VLOOKUP(8,Classes,5)),IF(B2=2003,IF(C2="No","",VLOOKUP(8,Classes,8)),IF(B2=2004,IF(C2="No","",VLOOKUP(8,Classes,11)),IF(B2=2005,IF(C2="No","",VLOOKUP(8,Classes,14)),IF(B2=2006,IF(C2="No","",VLOOKUP(8,Classes,17)),IF(B2=2007,IF(C2="No","",VLOOKUP(8,Classes,20)),IF(B2=2008,IF(C2="No","",VLOOKUP(8,Classes,23)),"Bugger")))))))

 

[J.E. McGimpsey]

You're limited in the number of functions you can nest to 7.

Of course, you could replace the whole thing with:

=IF(C2="No","",IF(AND(B2>=2002,
B2<=2008),VLOOKUP(8,Classes,(B2-2002)*3+5),"")