View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
RobinMelbourne RobinMelbourne is offline
external usenet poster
 
Posts: 2
Default overcome from the restriction of number of IF statements

I would consider replacing the IF's with a combination of INDEX and
MATCH, for example:

=INDEX($B$1:$B$10,MATCH($AA$2+1,$A$1:$A$10,-1))

this formula requires you to have a look up table as follows in
A1:B10....

100000 -
101 AA
90 BA
85 BB
80 CB
75 CC
70 DC
65 DD
60 FD
50 FF

You should get exactly the same result as per your IF statements.
Note that for the MATCH to work correctly the values must be in
descending order - look at the XL help file for a description of the
arguments.

Hope that helps, good luck.