Offset and Match in Dynamic Range

[goldenbear10]

Hi,

I have a formula that functions in a similar manner to vlookup, but instead of returning the next smallest value, it returns the next largest value.
Here is the formula, which is located on Sheet2:

Please Login or Register  to view this content.

The part of my table in which I am interested is C7:D11, and my reference value which can change is in C24 and the values I'm comparing my reference value to are in C7:C11.
My problem is that I have a macro that allows the user to add more rows to the table (i.e. the table can go from C7:D12, C7:D13, and so on), but my formula obviously doesn't change. Is there a way to use a macro so that the formula can accommodate new rows but without interfering with any data below my table? I know the solution when there is nothing below the table, but there is a blank row between this table and more data I do not want taken into account.

Thanks for the help.

[Palmetto]

Create dynamic names ranges for the look up references and use the named range in place of the hard-code range references.

OFFSET(INDEX(MyRange_1,MATCH('Sheet1'!$C$24,'Sheet1'!MyRange_2),2),1,)

Where "MyRange_1" and "MyRange_2" refer to named ranges using a formula similar to:
=OFFSET('Sheet1'!$C$7,0,0,COUNTA($C:$C),2)

See this link for help on creating dynamic named ranges