I need to compare items via interpolated Y-values in Google Sheets.

My table has about 50 items, each with 25 to 100 X/Y coordinates describing a line. While the line is nonlinear, linear interpolation between neighboring points is accurate enough for my needs.

item_name         coord_x       coord_y

firstthing            10             5 
firstthing            20             10
firstthing            27             15 
secondthing           10             4
secondthing           15             7

I'm using this script to do the interpolation:

How to interpolate data in a range in Google Sheets



to yield 7.5 

The problem: I don't want to manually define the range corresponding to each item or create intermediary columns duplicating the data (e.g., with VLOOKUP). I'd like the function to look for all rows that match a given item_name and apply the interpolation function only them. Sort of a VLOOKUP with an array return, formatted in a way that the script (or an alternative) will accept.

How can I do this?


"vlookup with an array return" is called filter. It works like this:

=myInterpolation(filter(B:B, A:A="firstthing"), filter(C:C, A:A="firstthing"), 15)
  • Awesome! Thank you! I'm disappointed I didn't catch that with my Google-fu. Do you know if it's possible to use this particular script as part of an array formula? It errors out when I try. This isn't critical, just curious. – Alex Dec 29 '17 at 5:11
  • With this modification, you can pass an array to it directly, like myInterpolation(..., ..., F2:F10). No need for arrayformula. – user135384 Dec 29 '17 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.