Will FLOOR & CEILING work with variable stratifications?

[johnu]

I am attempting to use a lookup formula to determine the next iteration in an
array.

For instance, if the cell = 21,000 then I need the result to be the value in
the array immediately lower than 21,000, in this case 20,000. In the next
cell, I need the result to be the value in the array immediately greater than
21,000, in this case 25,000.

The FLOOR and CEILING formulas would normally work but, the stratifications
are not static. The values in this array change from 1,000 to 2,000 to 5,000
to 10,000 in different places in the array.

The LOOKUP function seems to work for the value immediately less than the
cell value.

Unfortunately, for the greater than value, I am currently using nested IF
statements like
=IF(C7<10000,H7+1000,IF(C7<=20000,H7+2000,IF(C7<=50000,H7+5000,IF(C7>50000,H7+10000))))

This seems to work but there has to be a cleaner way that is more idiot
proof. Does anyone have any ideas?

Thanks

 

[yshridhar]

Try this formula
=H7+LOOKUP(C7,{1,10000,20001,50001},{1000,2000,5000,10000})
With best wishes
Sreedhar