Nested if using range names

[DianeG]

Hi

I know that you can use VLOOKUP instead of IF's to get a result but I'm
trying to get Chip Pearson's example to work. The problem is as follows:

I have a table that has values like this and I want to use an if statement
to test the cells but have exceeded the no of IF's allowed
B91
0 5,000
5,001 10,000
10,001 25,000
25,001 50,000
50,001 100,000
100,001 250,000
250,001 500,000
500,001 1,000,000
1,000,001 5,000,000
5,000,000

IF($F$79>=$B$91,$C$91,IF($F$79>=$B$90,$C$90,IF($F$79>=$B$89,$C$89,IF($F$79>=$B$88,$C$88,($F$79>=$B$87,$C$87,false)))))

IF($F$79>=$B$86,$C$86,IF($F$79>=$B$85,$C$85,IF($F$79>=$B$84,$C$84,IF($F$79>=$B$83,$C$83,$C$82))))

The suggestion was to split the If statements and name them and then join
them together in a 'master' statement like
=if(secondtHalf,secondHalf,firstHalf)
the half of the 'firstHalf' statement works OK independantly so does the
second half but when I join them together in the master statement it gives
the wrong values. How can I set this up so that it tests All the values and
not each individual half of the data separately? Is it possible?

Hope this makes sense, if not I'm happy to expand on it!

Thanks in advance

Diane

 

[Bernard Liengme]

To test what I was doing I put your table in B1:C9; and the number to test
in F1 not F79
This works:
=MAX(IF(F1>=B9,C9,IF(F1>=B8,C8,IF(F1>=B7,C7,IF(F1>=B6,C6,0)))),IF(F1>=B5,C5,IF(F1>=B4,C4,IF(F1>=B3,C3,IF(F1>=B2,C2,C1)))))
and can be extended
best wishes