VLOOKUP and Interpolating

Question

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

Solution 1

Use this (2.51 in D5 field)

=FORECAST(D5,OFFSET($B$1,MATCH(TRUE,$A$1:$A$100<=D5,0),,2),OFFSET($A$1,MATCH(TRUE,$A$1:$A$100<=D5,0),,2))

confirmed with ctrl+shift+enter (not just enter). It will consider also weighted average (i.e. different output for 2.51 and 2.505)

Solution 2

In the above image, A1:B3 contains your input data, column D contains the values you're looking for, and column E the lookup formula.

The formula in E5 is:

=IF(ISNA(VLOOKUP(D5, A:B, 2, FALSE)), AVERAGE(VLOOKUP(D5, A:B, 2, TRUE),MINIFS(B:B,B:B,">" &VLOOKUP(D5, A:B, 2, TRUE))), VLOOKUP(D5, A:B, 2, FALSE))

Formatting it for readability, it becomes:

1: =IF(
2:    ISNA(VLOOKUP(D5, A:B, 2, FALSE)), 
3:    AVERAGE(
4:        VLOOKUP(D5, A:B, 2, TRUE),
5:        MINIFS(B:B,B:B,">" &VLOOKUP(D5, A:B, 2, TRUE))
6:    ), 
7:    VLOOKUP(D5, A:B, 2, FALSE)
8: )

---

Explantion of the Formula

The line:

2: ISNA(VLOOKUP(D5, A:B, 2, FALSE)) 

returns TRUE if the VLOOKUP fails. This lookup fails only when an exact match is not found (since the last parameter is false, it looks for an exact match).

If the above ISNA() function on line 2 returns FALSE, then an exact match was found, and that value is returned by the statement:

7: VLOOKUP(D5, A:B, 2, FALSE) 

present in the last line.

However, if ISNA() on line 2 returns TRUE then an exact match was not found, resulting in an average (interpolation) being returned, by the following block:

3:    AVERAGE(
4:        VLOOKUP(D5, A:B, 2, TRUE),
5:        MINIFS(B:B,B:B,">" &VLOOKUP(D5, A:B, 2, TRUE))
6:    ), 

Here, the VLOOKUP() on line 4 is slightly different from the other two lookups - the last parameter is TRUE indicating a range lookup (inexact match). The documentation for VLOOKUP states that for a range lookup:

TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one.

When column A is sorted in ascending order, a range lookup for 2,51 returns the value corresponding to 2,50 (i.e. the lower value), namely 4523. This is the lower value for interpolation.

Line 5 gives us the higher value for interpolation:

5:        MINIFS(B:B,B:B,">" &VLOOKUP(D5, A:B, 2, TRUE)) 

It searches column B for the smallest value (using the MINIFS function) but applies the condition that the smallest value should be larger than the value found by the lookup in line 4. If line 4's lookup returned 4523, then this line searches for the smallest value in column B that is larger than 4523, which gives 4687. This is the upper limit for interpolation.

Once both these values are obtained, the AVERAGE function on line 3 returns the average value of 4523 and 4687, which is 4605.

---

Note 1: Do note that you will have to handle edge cases (such as 2,49 or 2,55) separately, the provided formula does not do that. I've not done so to keep this answer focused on your interpolation question.

Note 2: The above formula (specifically line 5) assumes that column B increases as the values in column A increases. If the values in column B do not increase in relation to the values in column A, then the MINIFS function will not return the correct value. In such a case, instead of the MINIFS function you'll have the MATCH and INDEX functions to find the value in the row that follows. i.e. line 5 would use the following formula (instead of MINIFS):

5: INDEX(B:B,MATCH(VLOOKUP(D5, A:B, 2, TRUE),B:B,0)+1) 

Comments

[Admin]

Bonus for making sure to mention that the values must be in ascending order!

[Solar Mike]

You provide the average of the two target values as the solution, but if the X value is skewed towards one end ie 2.508 then the average will not be as accurate and a weighted calculation can be used.

[OES]

This would be easier to implement to other ranges: =FORECAST(D5,OFFSET($A$1,MATCH(TRUE,$A$1:$A$100<=D5,0),1,2),‌​OFFSET($A$1,MATCH(TR‌​UE,$A$1:$A$100<=D5,0‌​),,2)) (Just change number 1 into desired offset column)

[Solar Mike]

Now that is neat - I had a whole bunch of vlookups to do this before - never thought of forecast, just shows the skills on this site... Thanks