You have a number of tables that have index values in the first row and first column of each table and a set of values in the interior of the table, each of which is associated with specific row and column index values.

Given two values that, respectively, may equal exactly a row or column index, or may lie between two row index values or two column index values, you want to do a straight-line interpolation of the values in the table based upon the two given values for the first row and first column.

To do the interpolation for input values that lie between the index values in the table, the following are needed:

where `vnum`

is the input value and `vrng`

is the vertical range of indexes in the first column of the table. The -1 third argument of the`MATCH`

function indicates that a "less than" lookup will be performed. This use of the MATCH function requires that the vertical range of indexes be in descending order.

The largest vertical index value that is smaller than (or equal) the vertical input value (`vnum`

). This cannot be obtained using the `MATCH`

function because the vertical index values are not sorted in the ascending order required by `MATCH`

. Instead, the following array formula is used.

`=MIN(IFERROR(1/(vnum>=vrng)*ROW(INDIRECT("1:"&ROWS(vrng))),ROWS(vrng)))`

The key element in this formula is `vnum>=vrng`

, which produces a boolean array in which the first `TRUE`

is in the row position that holds the largest vertical index value that is less than the input value. (The use of ">=" may seem counter-intuitive; it's needed because the indexes in the column are in descending order.) The remainder of the formula converts this row position into a row number.

These two upper and lower row numbers are used to calculate both the index values that bracket the input value above and below and the interior table values corresponding to those index values.

The corresponding column numbers and column values for the horizontal index range are constructed in a similar fashion.

The remaining steps work through the arithmetic of the interpolation.

With this many steps, it may seem that calculating the interpolated amounts for multiple input values would be impractical. It's actually quite easy using a two-way data ("what-if") table.

The setup of these calculations for multiple tables can be simplified even further by using the one-formula version of them. To use it, the named ranges `vnum`

, `hnum`

, `vrng`

, `hrng`

, `datarng`

, `validvnum`

, and `validhnum`

would need to be set up. The tables need to be in separate sheets or in separate workbooks. If in separate sheets, the names for each sheet must be set to have worksheet scope.

The single-step calculation formula would then be entered in the top left cell of the data table. This 2,100 (minus one)-character formula is included in the downloadable worksheet.

A worksheet containing this set of calculations can be downloaded using this link.

6more comments