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:

OFFSET(INDEX('Sheet1'!C7:D11,MATCH('Sheet1'!$C$24,'Sheet1'!C7:C11),2),1,)
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.