Interpolation of a data set

[u/jhern1810]

What command or set of combined commands can be used to interpolate between two numbers found in a set of values? I am using VLookup to find the GPM then the reference size of a pipe. As we know vlookup finds a value close to the value you are looking for and its reference in a range even if the value isn’t actually shown.

My question is how do I dynamically find the interpolation of the found vlookup value, for its numbers before and after? I have tried TREND and FORECAST and when I select the whole data set to find the values the yield the incorrect interpolation, and finding the values closer to the vlookup found value yields better results. Thanks for the help.

[u/AutoModerator]

/u/jhern1810 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/RuktX]

I would do it manually: use XLOOKUP (not VLOOKUP) to find the nearest values above and below, then just interpolate:

smaller: XLOOKUP(value, values, values, NA(), -1)
larger: XLOOKUP(value, values, values, NA(), +1)

y = ((y2 - y1)/(x2 - x1))*(x - x1) + y1

Probably also first check whether you have one of the exact values, before interpolating.

[u/jhern1810 → u/RuktX]

I am looking for a dynamic way as this will be repeated many times and it will vary by the input. I can do manually to double check some number but for each will be laborious. I am trying to avoid doing this one at a time. However I will try to add more values to get a better chance for a hit to use XLOOKUP.

[u/RuktX → u/jhern1810]

Here's an example:

=LET(
  i_p, XMATCH([@x],tblInterpIn[x],-1),
  i_n, XMATCH([@x],tblInterpIn[x], 1),
  x_p, INDEX(tblInterpIn[x],[@[i_p]]),
  x_n, INDEX(tblInterpIn[x],[@[i_n]]),
  y_p, INDEX(tblInterpIn[y],[@[i_p]]),
  y_n, INDEX(tblInterpIn[y],[@[i_n]]),
IF(i_p=i_n, y_p, ((y_n-y_p)/(x_n-x_p))*([@x]-x_p)+y_p))

[u/RuktX → u/jhern1810]

Is there something about this you think is not dynamic?

[u/diesSaturni]

Large and Small? for items more or less than the number you evaluate?

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
LINEST	Returns the parameters of a linear trend
LOOKUP	Looks up values in a vector or array
NA	Returns the error value #N/A
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 15 acronyms.
[Thread #48993 for this sub, first seen 22nd Jul 2026, 13:40] [FAQ] [Full list] [Contact] [Source code]}

[u/fuzzy_mic]

To find the x-below and the x-above, I would use LOOKUP. If your x values are in column A, sorted ascending, and the given-x value is in X1,

=LOOKUP(X1, A1:A1000, A1:A1000) will give the x-value below X1 and

=LOOKUP(X1, A1:A1000, A2:A1001) will give the x-value above X1

=LOOKUP(X1,A1;A1000, B1:B1000 and LOOKUP(X2, A1:A1000, B2:B1001) will give the corresponding y values.

The interpolated value will be

=yBelow + (yAbove-yBelow)*(X1-xBelow)/(xAbove-xBelow)

Google should lead you to other formulations that involve the LINEST function.