VLOOKUP and Interpolating

excel excel-formula interpolation vlookup
user2737015 picture user2737015 · Mar 18, 2017 · Viewed 14.3k times · Source

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           4523
2,52           4687
2,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

Answer

user4039065 picture user4039065 · Mar 18, 2017

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

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More