How to Do VLOOKUP and Interpolate in Excel: 6 Methods

Method-1 – Using the IFERROR, VLOOKUP, and AVERAGE Functions to Do VLOOKUP and Interpolate

Steps:
➤ Type the following formula in cell F5.

E5 is the look-up value, $C$4:$C$11 is the range of days numbers where we will look up value and $D$4:$D$11 is the range of the sales values.

➤ Press ENTER.
You will get 7,353.00 for day 12, which is the interpolation of the sales values between days 11 and 13.

Method-2 – Combination of IF, ISNA, VLOOKUP, AVERAGE, and MINIFS Functions to Do VLOOKUP and Interpolate

➤ Type the following formula in cell F5.

E5 is the look-up value, $C$4:$C$11 is the range of days numbers where we will look up value, and $D$4:$D$11 is the range of the sales values.

After pressing ENTER, you will get the sales value of $5,298.50 for day 8 by looking up the values in the range and interpolating between the sales values of days 7 and 9.

Method-3 – Using the Combination of the IF, ISNA, VLOOKUP, AVERAGE, INDEX, and MATCH Functions

➤ Type the following formula in cell F5.

E5 is the look-up value, $C$4:$C$11 is the range of days numbers where we will look up value, and $D$4:$D$11 is the range of the sales values.

➤ Press ENTER.
You will get 7,353.00 for day 12, which is the interpolation of the sales values between days 11 and 13.

Method-4 – Do VLOOKUP and Interpolate in Excel Using the FORECAST, OFFSET, and MATCH Functions

➤ Use the following formula in cell F5.

E5 is the look-up value, $C$4:$C$11 is the range of days numbers where we will look up value, and $D$4:$D$11 is the range of the sales values.

After pressing ENTER, you will get the sales value of $7,353.50 for day 12 by looking up the values in the range and interpolating between the sales values of days 11 and 13.

Method-5 – Using the Combination of INDIRECT, ADDRESS, and MATCH Functions

➤ Apply the following formula in cell F5.

E5 is the look-up value, $C$4:$C$11 is the range of days numbers where we will look up value, and $D$4:$D$11 is the range of the sales values.

➤ Press ENTER.
You will get the sales value of $5,298.50 for day 8 by looking up the values in the range and interpolating between the sales values of days 7 and 9.

Method-6 – Using VBA Code to Do VLOOKUP and Interpolate in Excel

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

The Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

A Module will be created.

➤ Write the following code

Function vlookupforintermediates(day As Variant, dayrange As Variant, _
salesrange As Variant) As Double

Dim j, small_day, large_day As Variant
small_day = WorksheetFunction.Min(dayrange.Value)
large_day = WorksheetFunction.Max(dayrange.Value)
Dim increment, small_increment, large_increment As Integer
small_increment = 0
large_increment = 0
increment = 0

For Each j In dayrange
increment = increment + 1
If j.Value = day Then
vlookupforintermediates = salesrange(j).Value
Exit Function
End If
Next j

increment = 0

For Each j In dayrange
increment = increment + 1
If j.Value < day Then
   If j.Value >= small_day Then
   small_day = j.Value
   small_increment = increment
   End If
End If
If j.Value > day Then
   If j.Value <= large_day Then
   large_day = j.Value
   large_increment = increment
   End If
End If
Next j

vlookupforintermediates = salesrange(small_increment).Value + (day - small_day) * _
(salesrange(large_increment).Value - salesrange(small_increment).Value) _
/ (large_day - small_day)

End Function

We created a function with the name vlookupforintermediates, and the variables under this function as inputs are day, dayrange, and salesrange all of them are declared as Variant. We declared j, small_day, and large_day as Variant and increment, small_increment, and large_increment as Integer.
Using the MIN and MAX functions small_day and large_day are assigned to the smallest and largest days.
The first FOR loop will return the sales values for any day which is present in the day’s ranges and the second FOR loop with the IF-Then statement will store value to the small_increment and large_increment variables.
Using the formula for calculating interpolation, this function will return our desired value.

After saving the code, you have to return to the main sheet.
➤ Use the following formula in cell F5.

The vlookupforintermediates is the created function name, E5 is the day, C4:C11 is the dayrange, and  D4:D11 is the salesrange.

➤ Press ENTER.
You will get the sales value of $7,353.00 for the specific day no. 12.

Download Workbook

Related Articles

• How to Do Linear Interpolation in Excel
• How to Interpolate Missing Data in Excel
• How to Do Interpolation with GROWTH & TREND Functions in Excel
• How to Interpolate Between Two Values in Excel
• How to Perform Bilinear Interpolation in Excel
• How to Use Non Linear Interpolation in Excel

<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel