How to interpolate values in Excel

I have two tables. The first contains indicative conditional rates for a specific number of days (column headings)

And the second table in which I need to calculate the real rate for a given number of days

As you can see, conditional rates are given for some days, and real rates for others. Among other things, conditional rates are given for each day, and real rates only in some of them. How can I calculate this rate? I see the use of linear interpolation here, for example - if the real number of days is 21, then the nearest conditional rate is given for 14 days and I need to extrapolate this value several days in future. I read about =forecast, but it not do what I need. Is there a function in Excel that will help me do this or do I need to write a VBA script for this?

Office 365:

=FORECAST.LINEAR(B7,
 INDEX(B2:F2,MATCH(B7,$B$1:$F$1,1)):INDEX(B2:F2,MATCH(B7,$B$1:$F$1,1)+1),
 INDEX($B$1:$F$1,MATCH(B7,$B$1:$F$1,1)):INDEX($B$1:$F$1,MATCH(B7,$B$1:$F$1,1)+1))

From Interpolate with Excel

Surprisingly, linear interpolation is quite hard to do in Excel.

This uses Excel Forecast function =FORECAST(x,known_y's,known_x's).

The =INDEX and =MATCH pick out the relevant two points on known_y's and known_x's.

This will work on Excel 2007, but =FORECAST.LINEAR must be changed to =FORECAST (depreciated soon).

we need to get a ratio of where the days land between the set point and multiply that ratio to the difference between the upper and lower values, then add the lower value to that result.

One step that must take place is changing the headers to the actual number.

In OFFICE 365 we can use LET to simplify the steps:

=LET(head,$B$1:$F$1,
         lkp,B7,
         rng,$B$2:$F$4,
         dt,$A$2:$A$4,
         rw,INDEX(rng,MATCH(A7,dt,0),0),
         mtch,MATCH(lkp,head),
         mtchp1,mtch+1,
         fst,INDEX(head,,mtch),
         scd,INDEX(head,,mtchp1),
         fct,(lkp-fst)/(scd-fst),
          vlue1,INDEX(rw,,mtch),
          vlue2,INDEX(rw,,mtchp1),
          (vlue2-vlue1)*fct+vlue1)

without the let:

=(INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1)+1)- INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1)))* ((B7- INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1)))/( INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1)+1)- INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1))))+ INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1))

As you can see, it becomes very unwieldy and hard to manage.

Another method with the LET is to use FORECAST.LINEAR instead of math to get the answer after getting the start and end x's and y's:

=LET(head,$B$1:$F$1,
         lkp,B7,
         rng,$B$2:$F$4,
         dt,$A$2:$A$4,
         rw,INDEX(rng,MATCH(A7,dt,0),0),
         mtch,MATCH(lkp,head),
         mtchp1,mtch+1,
         fst,INDEX(head,,mtch),
         scd,INDEX(head,,mtchp1),
          vlue1,INDEX(rw,,mtch),
          vlue2,INDEX(rw,,mtchp1),
          FORECAST.LINEAR(lkp,CHOOSE({1,2},vlue1,vlue2),CHOOSE({1,2},fst,scd)))