How to Do VLOOKUP and Interpolate in Excel (6 Ways)

Sometimes you may need to look up for a value in a range that doesn’t remain exactly in the range rather than locates between two values, in this case, we need to do VLOOKUP and interpolate in Excel simultaneously.
This article will enlighten you about different ways of doing VLOOKUP and interpolation at a time for having desired values.

Download Workbook


6 Ways to Do VLOOKUP and Interpolate in Excel

Here, we have the sales records of a product with the product code 1234XD5 for different days but the serial numbers of days are arranged for every other day. So, for knowing the sales values between these days we cannot be dependent only on the VLOOKUP function but also we need to interpolate them for getting the exact value for that day.
The following 6 methods will illustrate the procedures of doing this job using different combinations of functions along with a VBA code.

how to do VLOOKUP and interpolate in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


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

Here, we will calculate the sales value for Day No. 12 by interpolating between the values of Day No. 11 and Day No. 13. For this purpose, we will use the IFERROR function, VLOOKUP function, AVERAGE function, OFFSET function, INDEX function, and MATCH function.

how to do VLOOKUP and interpolate in Excel

Steps:
➤ Type the following formula in cell F5.

=IFERROR(VLOOKUP(E5,$C$4:$D$11,2,FALSE),AVERAGE(OFFSET(INDEX($D$4:$D$11,
MATCH(E5,$C$4:$C$11,1)),0,0,2,1)))

Here, 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.

  • VLOOKUP(E5,$C$4:$D$11,2,FALSE) becomes
    VLOOKUP(12,$C$4:$D$11,2, FALSE) → checks if 12 presents in the range $C$4:$C$11 and then returns the corresponding sales values if it finds an exact match otherwise returns #N/A error.
    Output → #N/A
  • MATCH(E5,$C$4:$C$11,1) → gives the row index number for a day no. immediate less than 12 corresponding to the dataset and here 1 is for finding the closest small value of 12.
    Output → 6
  • INDEX($D$4:$D$11,MATCH(E5,$C$4:$C$11,1)) becomes
    INDEX($D$4:$D$11,6) → returns the cell reference number according to the row index number 6 from the range $D$4:$D$11.
    Output → $D$9
  • OFFSET(INDEX($D$4:$D$11,MATCH(E5,$C$4:$C$11,1)),0,0,2,1) becomes
    OFFSET($D$9,0,0,2,1) → extracts a range with a height 2 starting from the cell $D$9.
    Output → $D$9:$D$10
  • AVERAGE(OFFSET(INDEX($D$4:$D$11,MATCH(E5,$C$4:$C$11,1)),0,0,2,1))) becomes
    AVERAGE($D$9:$D$10) → returns the average of the values of this range
    Output → 7353
  • IFERROR(VLOOKUP(E5,$C$4:$D$11,2,FALSE),AVERAGE(OFFSET(INDEX($D$4:$D$11,MATCH(E5,$C$4:$C$11,1)),0,0,2,1))) becomes
    IFERROR(#N/A,7353) → for errors return the second argument 7353.
    Output → $7,353.00

IFERROR, VLOOKUP, AVERAGE Functions

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

IFERROR, VLOOKUP, AVERAGE Functions


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

For looking up the sales value for Day No. 8 we are going to use the combination of the IF function, ISNA function, VLOOKUP function, AVERAGE function, and MINIFS function here.

how to do VLOOKUP and interpolate in Excel

➤ Type the following formula in cell F5.

=IF(ISNA(VLOOKUP(E5, $C$4:$D$11, 2, FALSE)), AVERAGE(VLOOKUP(E5, $C$4:$D$11, 2, TRUE),MINIFS($D$4:$D$11,$D$4:$D$11,">" &VLOOKUP(E5, $C$4:$D$11, 2, TRUE))), VLOOKUP(E5, C4:D11, 2, FALSE))

Here, 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.

  • VLOOKUP(E5, $C$4:$D$11, 2, FALSE) becomes
    VLOOKUP(8,$C$4:$D$11,2, FALSE) → checks if 8 presents in the range $C$4:$C$11 and then returns the corresponding sales values if it finds an exact match otherwise returns #N/A error.
    Output → #N/A
  • ISNA(VLOOKUP(E5, $C$4:$D$11, 2, FALSE)) becomes
    ISNA(#N/A) → returns TRUE for #N/A error otherwise FALSE.
    Output → TRUE
  • VLOOKUP(E5, $C$4:$D$11, 2, TRUE) → finds an approximate match smallest near value presents in the range like for day 8 it will give the sales value for day 7
    Output → 4662
  • MINIFS($D$4:$D$11,$D$4:$D$11,”>” &VLOOKUP(E5, $C$4:$D$11, 2, TRUE) ) becomes
    MINIFS($D$4:$D$11,$D$4:$D$11,”>” &4662) →  MINIFS($D$4:$D$11,$D$4:$D$11,”>4662″) → returns the minimum value from the range $D$4:$D$11 based on the criteria that the values greater than 4662 in this range
    Output → 5935
  • AVERAGE(VLOOKUP(E5, $C$4:$D$11, 2, TRUE),MINIFS($D$4:$D$11,$D$4:$D$11,”>” &VLOOKUP(E5, $C$4:$D$11, 2, TRUE))) becomes
    AVERAGE(4662, 5935) → returns the average of these values
    Output → 5298.5
  • IF(ISNA(VLOOKUP(E5, $C$4:$D$11, 2, FALSE)), AVERAGE(VLOOKUP(E5, $C$4:$D$11, 2, TRUE),MINIFS($D$4:$D$11,$D$4:$D$11,”>” &VLOOKUP(E5, $C$4:$D$11, 2, TRUE))), VLOOKUP(E5, C4:D11, 2, FALSE)) becomes
    IF(TRUE, 5298.5, #N/A) → returns 5298.5 for TRUE otherwise #N/A
    Output → $5,298.50

IF, ISNA, VLOOKUP, MINIFS Function

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.

IF, ISNA, VLOOKUP, MINIFS Function


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

In this section, we are going to use the  IF function, ISNA function, VLOOKUP function, AVERAGE function, INDEX function, and MATCH function for searching up the sales value for the day no 12 and interpolating the values for days 11 and 13.

how to do VLOOKUP and interpolate in Excel

➤ Type the following formula in cell F5.

=IF(ISNA(VLOOKUP(E5,$C$4:$D$11,2,FALSE)),AVERAGE(VLOOKUP(E5,$C$4:$D$11,2,TRUE),INDEX($D$4:$D$11,MATCH(VLOOKUP(E5,$C$4:$D$11,1,TRUE),$C$4:$C$11,1)+1)),
VLOOKUP(E5,$C$4:$D$11,2,FALSE))

Here, 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.

  • VLOOKUP(E5, $C$4:$D$11, 2, FALSE) becomes
    VLOOKUP(12,$C$4:$D$11,2, FALSE) → checks if 12 presents in the range $C$4:$C$11 and then returns the corresponding sales values if it finds an exact match otherwise returns #N/A error.
    Output → #N/A
  • ISNA(VLOOKUP(E5, $C$4:$D$11, 2, FALSE)) becomes
    ISNA(#N/A) → returns TRUE for #N/A error otherwise FALSE.
    Output → TRUE
  • VLOOKUP(E5, $C$4:$D$11, 2, TRUE) → finds an approximate match smallest near value presents in the range like for day 12 it will give the sales value for day 11
    Output → 6835
  • VLOOKUP(E5,$C$4:$D$11,1,TRUE)
    Output → 11
  • MATCH(VLOOKUP(E5,$C$4:$D$11,1,TRUE),$C$4:$C$11,1) becomes
    MATCH(11,$C$4:$C$11,1) → gives the row index number for a day no. immediate less than 12 corresponding to the dataset and here 1 is for finding the closest small value of 12.
    Output → 6
  • MATCH(VLOOKUP(E5,$C$4:$D$11,1,TRUE),$C$4:$C$11,1)+1 becomes
    6+1 → adds up 1 for getting the row index number of the day no. 13
    Output → 7
  • INDEX($D$4:$D$11,MATCH(VLOOKUP(E5,$C$4:$D$11,1,TRUE),$C$4:$C$11,1)+1)) becomes
    INDEX($D$4:$D$11,7) → returns the cell reference number according to the row index number 7 from the range $D$4:$D$11.
    Output → $D$10
  • AVERAGE(VLOOKUP(E5,$C$4:$D$11,2,TRUE),INDEX($D$4:$D$11,MATCH(VLOOKUP(E5,$C$4:$D$11,1,TRUE),$C$4:$C$11,1)+1)) becomes
    AVERAGE(6835,$D$10) → AVERAGE(6835,7871)
    Output → 7353
  • IF(ISNA(VLOOKUP(E5,$C$4:$D$11,2,FALSE)),AVERAGE(VLOOKUP(E5,$C$4:$D$11,2,TRUE),INDEX($D$4:$D$11,MATCH(VLOOKUP(E5,$C$4:$D$11,1,TRUE),$C$4:$C$11,1)+1)),VLOOKUP(E5,$C$4:$D$11,2,FALSE)) becomes
    IF(TRUE, 7353, #N/A) → returns 7353 for TRUE otherwise #N/A
    Output → $7,353.00

IF, ISNA, VLOOKUP, INDEX-MATCH Function

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

IF, ISNA, VLOOKUP, INDEX-MATCH Function

Read More: How to Interpolate Between Two Values in Excel (6 Ways)


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

Here, we will be using the FORECAST function, OFFSET function, and MATCH function for looking up the sales value for the day no 12 with the help of the interpolation.

how to do VLOOKUP and interpolate in Excel

➤ Use the following formula in cell F5.

=FORECAST(E5,OFFSET($D$4:$D$11,MATCH(E5,C4:C11,1)-1,0,2),OFFSET($C$4:$C$11,
MATCH(E5,$C$4:$C$11,1)-1,0,2))

Here, 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.

  • MATCH(E5,C4:C11,1) becomes
    MATCH(12, C4:C11,1) → gives the row index number for a day no. immediate less than 12 corresponding to the dataset and here 1 is for finding the closest small value of 12.
    Output → 6
  • MATCH(E5,C4:C11,1)-1 becomes
    6-1 → 5
  • OFFSET($D$4:$D$11,MATCH(E5,C4:C11,1)-1,0,2) becomes
    OFFSET($D$4:$D$11,5,0,2) → firstly the new reference is set to the cell $D$9 after moving 5 rows downwards from $D$4 and then extracts a range of height 2 from the cell $D$9
    Output → {6835; 7871}
  • OFFSET($C$4:$C$11,MATCH(E5,$C$4:$C$11,1)-1,0,2) becomes
    OFFSET($C$4:$C$11,5) → firstly the new reference is set to the cell $C$9 after moving 5 rows downwards from $C$4 and then extracts a range of height 2 from the cell $C$9
    Output → {11; 13}
  • FORECAST(E5,OFFSET($D$4:$D$11,MATCH(E5,C4:C11,1)-1,0,2),OFFSET($C$4:$C$11,MATCH(E5,$C$4:$C$11,1)-1,0,2)) becomes
    FORECAST(12,{6835; 7871},{11; 13}) → gives the value after doing interpolation
    Output → $7,353.00

FORECAST, OFFSET, MATCH Functions

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.

FORECAST, OFFSET, MATCH Functions

Read More: How to Do Linear Interpolation in Excel (7 Handy Methods)


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

In this section, we will calculate the sales value for the day no. 8 by using the INDIRECT function, ADDRESS function, and MATCH function for doing the interpolation and searching the value in the range.

how to do VLOOKUP and interpolate in Excel

➤ Apply the following formula in cell F5.

=INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4))+(INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,4))-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4)))/
(INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,3))-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3)))*
(E5-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3)))

Here, 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.

  • MATCH(E5,$C$4:$C$11,1)gives the row index number for a day no. immediate less than 8 corresponding to the dataset and here 1 is for finding the closest small value of 8.
    Output → 4
  • MATCH(E5,$C$4:$C$11,1)+3 becomes
    4+3 → 3 is added here because our dataset has started after Row 3
    Output → 7
  • ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4) becomes
    ADDRESS(7,4)returns the cell reference for a cell with row 7 and column 4
    Output → “$D$7”
  • INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4)) becomes
    INDIRECT(“$D$7”)returns the value of this cell
    Output → 4662
  • MATCH(E5,$C$4:$C$11,1)+4 becomes
    4+4 → 3 (3+1=4) is added here because our dataset has started after Row 3 and another 1 is for having the value for day 9
    Output → 8
  • INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,4)) becomes
    INDIRECT(ADDRESS(8,4))INDIRECT(“$D$8”)
    Output → 5935
  • INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,4))-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4)) becomes
    5935-4662
    Output → 1273
  • INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,3) becomes
    INDIRECT(ADDRESS(8,3)) → INDIRECT(“$C$8”)
    Output → 9
  • INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3) becomes
    INDIRECT(ADDRESS(7,3))INDIRECT(“$C$7”)
    Output → 7
  • (INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,3))-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3))) becomes
    9-7
    Output → 2
  • E5-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3)) becomes
    8-7
    Output → 1
  • INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4))+(INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,4))-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,4)))/(INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+4,3))-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3)))*(E5-INDIRECT(ADDRESS(MATCH(E5,$C$4:$C$11,1)+3,3))) becomes
    4662+((1273)/(2))*1
    Output → $5,298.50

INDIRECT, ADDRESS, MATCH Functions

➤ Press ENTER.
Finally, 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.

INDIRECT, ADDRESS, MATCH Functions


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

If you are fond of VBA and want to do VLOOKUP and Interpolate in Excel using VBA, then this method will be a handy one for you. In this method, we will use a VBA code for searching a value in a range, and then, get the desired result with the help of the interpolation.

how to do VLOOKUP and interpolate in Excel

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

VBA Code

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

how to do VLOOKUP and interpolate in Excel

After that, a Module will be created.

VBA Code

➤ 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

Here, we have 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. Moreover, we have 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 day and largest day.
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.
Finally, using the formula for calculating interpolation this function will return our desired value.

VBA Code

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

=vlookupforintermediates(E5,C4:C11,D4:D11)

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

VBA Code

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

how to do VLOOKUP and interpolate in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Conclusion

In this article, we tried to show the ways to do VLOOKUP and interpolate in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo