Excel – VLOOKUP and Interpolating

excel, excel-formula, interpolation, vlookup

I am trying to check a table for specific data and if i found the data it will display the data. I did that with VLOOKUP. But now if the data is not in the table i want to interpolate between two sets of data. But i have no idea how to do it.

So what i want to archieve is something that check if a number is in the table and if its not it needs to interpolate.

Exapmle:

2,50           45232,52           46872,54           4790

I want: 2,50
Display: 4523

I want: 2,51 (It isnt there i want to interpolate (4687+4523)/2)

Display: the interpolated number

EDIT:

Vlookup formula:

=VLOOKUP(F3;Tabel3;2;FALSE)

EXCEL Screenshot

enter image description here

Best Solution

IFERROR can pass processing to another formula if the VLOOKUP fails. If the lookup values (2,50; 2,52; 2,54) are true numbers in ascending order then MATCH with 1 as the range_lookup parameter will retrieve the row number of the lower value. Use OFFSET to achieve a range for AVERAGE.

=IFERROR(VLOOKUP(F3,A:B,2,FALSE),AVERAGE(OFFSET(INDEX(B:B,MATCH(F3,A:A,1)),0,0,2,1)))

enter image description here