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?

1 Answer 1


"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
    Commented Dec 29, 2017 at 5:11
  • With this modification, you can pass an array to it directly, like myInterpolation(..., ..., F2:F10). No need for arrayformula.
    – user135384
    Commented Dec 29, 2017 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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