EXCEL - Dual VLOOKUP and Interpolation

Question

I have a table on Excel with data as the following:

Meaning, I have different JPH based on the %SMALL unit and the number of active stations.

I need to create a matrix like the following (with %SMALL on horizontal and STATIONS on vertical axes):

And the formula for each cell should:

Take the input of Stations (column "B")
Check, for that specific Stations number, the amount of data on the other table (like make a filter on STATIONS for the specific number)
Perform an VLOOKUP for checking the JPH based on the %SMALL value on row 2
Interpolate for the exact JPH value, if not found on table

For now, I was able to create the last part (the VLOOKUP and the interpolation), with the following:

=IFERROR(VLOOKUP(C2;'EARLY-STATIONS'!$F:$H;3;FALSE);AVERAGE(OFFSET(INDEX('EARLY-STATIONS'!$H:$H;MATCH(C2;'EARLY-STATIONS'!$F:$F;1));0;0;2;1)))

The problem I'm facing is than with this, the calculation is not checking the number of stations, so the Iteration is not accurate.

Unfortunately I cannot use VBA macros to solve this.

Any clue?

Miguel, I think it is doable, I have one question, what do you mean by interpolation? My understanding is that you look for the station number, then you find the %SMALL and enter the corresponding JPH value, but the interpolation confused me. Thanks — David Leal, Feb 01 '23 at 03:17
I mean than, if for example there is 34% of SMALL and 39% of SMALL for a specific number of stations, on 35-36-37-38% the code should find the JPH doing a progression — Miguel Gutiérrez de Antón, Feb 01 '23 at 04:11
Understood, but you are assuming there are more than one value to extrapolate from the Lookup table, what about if there is a single value only? How do you do the extrapolation on this case? You would need at least two values. How to handle this scenario? Are we going to repeat the reference value? You are using `AVERAGE`, this is not an extrapolation. Extrapolation would mean you can assume a linear function for example and you extrapolate the new value that you don't have it assuming a linear equation. Are the stations unique values? — David Leal, Feb 01 '23 at 04:44
If there is only one value, a Null result (or blank) could be possible without any problem. And you're absoultely right, I used the wrong word sorry hehe There is no need to create a linear function, the average result is enough on this case. Also, the stations are not unique values, there will be several entries for 4 stations, for 5... Max number is 39 — Miguel Gutiérrez de Antón, Feb 01 '23 at 05:10
Interpolation, assuming a straight line relationship, is as simple as for a given x value getting the x & y values for just below and just above then calculation the target y value. Just need to employ vlookup() several times. — Solar Mike, Feb 01 '23 at 07:36

score 0 · Answer 1 · answered Feb 01 '23 at 19:47

This is an attempt because more clarity is needed in terms of all possible scenarios to consider, based on different input data and how to understand the "extrapolation" process. This approach understands as extrapolation the average of two values (lower and greater), but the idea can be customized to any other way to calculate it. Per tags listed in the question I assume there is no Excel version constraint. This is O365 solution:

=LET(sm, A2:A10, st, B2:B10, jph, C2:C10, smx, F1:J1,  sty, E2:E4, NULL, "",
  GETLk, LAMBDA(x,y,mode, FILTER(jph, (st=y)
   * (sm = INDEX(sm, XMATCH(x, sm, mode))), NULL)),
  GET, LAMBDA(x,y, LET(f, FILTER(jph, (jph=GETLk(x,y, 1))
   + (jph=GETLk(x,y, -1)), NULL), IF(@f=NULL, NULL, AVERAGE(f)))),
  HREDUCE, LAMBDA(yi, DROP(REDUCE("", smx, LAMBDA(ac,x,
    HSTACK(ac, GET(x, yi)))),,1)),
  DROP(REDUCE("", sty, LAMBDA(ac,y, VSTACK(ac, HREDUCE(y)))),1))

The above formula spills the entire result, I don't think for this case you can use a LOOKUP-like function.

Here is the output:

The highlighted cells where the average is calculated.

Explanation

The main idea is to use DROP/REDUCE/HSTACK/VSTACK pattern to generate the grid. Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length on how to apply it.

We use two user LAMBDA functions to abstract some calculations:

GETLk(x,y,mode), filters jph name based on %SMALL and Stations columns values, based on input values x (x-axis value from the grid), y (y-axis value form the grid) respectively. The third input argument mode, is for doing the approximate search in XMATCH (1-next largest, -1 next smallest). In case the value exist in the input table, XMATCH returns the same value in both cases.
GET(x,y) has the logic to find the value or if the value doesn't exist to calculate the average. It uses the previous LAMBDA function GETLk. We filter for jph values that match the input values (x,y), but we use an OR condition in the FILTER (+), to select both lower or greater values. If the value exist, returns just one value otherwise two values are returned by FILTER (f). Finally if f is not empty we return the average, otherwise the value we setup as NULL.
HREDUCE: Concatenate the result by columns for a given row of the grid. Check the referred question for more information about it.

EXCEL - Dual VLOOKUP and Interpolation

1 Answers1

Explanation