is there an alternative to VLOOKUP which interpolates

[benwallace100]

Hi All

I am using Vlookup formula, but the second column of data (the data which vlookup returns) is very noisy, and instead of choosing the nearest datapoint I would rather do a linear interpolation between the nearest 2 datapoints...

What is the easiest way to do this?

Many thanks in advance.

 

[Phox]

See if this formula is to your liking. LRANGE is defined as the lookup column, DRANGE is the data column, and VALUE is the lookup value.

=(INDEX(DRANGE,MATCH(VLOOKUP(VALUE,LRANGE:DRANGE,2),DRANGE)+1)+VLOOKUP(VALUE,LRANGE:DRANGE,2))/2