A program to do that is easy, but actually I think you can do it with the
VLOOKUP function. Let me look it up for a second....No, VLOOKUP is willing
only to take the next smaller value, whereas you want the next larger. For
that you have to use MATCH:

=MATCH(C2,C4:C99,-1)

With -1 as the third argument, MATCH needs C4:C99 to be in DESCENDING order
and it will find the value that is equal to or just greater than C2. If the
values in C4:C99 are 500, 400, 300, 200 and 100, then MATCH will find the 300
value, which will be in C6; that's the third row of the table so it returns
3. You turn than into the address D6 by using the INDEX function, like this:

=INDEX(C4:D99,MATCH(C2,C4:C99,-1),2)

That way if MATCH returns 3, then INDEX looks at row 3 col 2 of C4:D99,
which is just the value you want. Forget the long complex IFs, and forget
VBA programming too; this should be simpler.

If for some reason you still want to do a VBA program, it's still pretty
easy. But I always prefer formulae and functions when possible, and I
imagine you do too.

--- "jat" wrote:
i have the following in cell D2:

=IF(C2<C4,D4,IF(C2<C5,D5,IF(C2<C6,D6,IF(C2<C7,D7,I F(C2<C8,D8,D9)))))

the problem is sometimes the min and max values will change and the
amount of brackets will also increase or decrease. i need a macro that
will autopopulate cell D2 - if possible;