Using range offset and Hlookup in an interpolation function

I am trying to write a function in VBA that linearly interpolates between values in a 2D table. So for a given target value in the first column the function will read the values in the first column and return an interpolated value from the associated values in the second column. I wanted to simplify the input arguments to just the target value (TargetValue) and the range of the table (ArrayRange).

Any example table is here.

Independent Variable	Dependent Variable
0	1
50	1.3
200	1.4

So, if I wanted to interpolate 65 the value would be 1.31. Calculation: (((1.4 - 1.3)/(200 - 50) * (65 - 50)) + 1.3).


Here is my code:

Please Login or Register  to view this content.

To linearly interpolate I need the values of the two independent variables above and below the target value (i_in and f_in), in the table, and their associated dependent variables (i_d and f_d). As it is a 2D table it is easy to obtain i_in and i_d using the VLOOKUP function, as omitting the final argument makes VLOOKUP find the largest value below the target value 'rounds down' (or the functions first argument).

The difficulties begin when I attempt to find the values of f_in and f_d. VLOOKUP can't be used as it only rounds down, so I set out to use HLOOKUP in conjunction with it (As an aside - if there is a way to get VLOOKUP to round up then I wouldn't have to go into the messy business of HLOOKUP and loops...). However, as VLOOKUP only reads the values in the first column of a range, HLOOKUP does the same with the first row (which incidentally means that the function works for target values less than the 2nd entry in column 1 of the table). But I want the function to work for any target value between the first entry in the first column of the table and the last. Hence I added a Do While loop.

I wanted the loop to move the range down by one row (using the Offset(rows,cols) function) every time that the value returned by the VLOOKUP was not found in the top row of the range (I understand that HLOOKUP returns a #N/A in this instance). Hence, when the value was found in a row, it would then become the first row of the new range and the HLOOKUP function could be used successfully to return the values of f_in and f_d. That was the plan, however, for some reason excel doesn't like the way I coded it and so this is where I need some help or advice. I suspect that the problem lies with the Offset loop - but am not certain.

I've been trying to get a working function for the past few days with varying amounts of success; using various other structures and strategies - but I think that this is the closest I have got so far. Perhaps a completely different tack would be better - maybe using the MATCH and/or INDEX functions (I've only looked into them briefly)?

I started VBA programming last Friday, so a moderate amount of patience with any 'obvious' idiocies would be appreciated.

Thanks in advance for any help.

Put it down to massive inexperience!

I tried a similar form of the function before - but did not know how to return the range of a cell (which you have done using Cells and Match).

Your function works perfectly (with the correction) - thank you ever so much!