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:
=myInterpolation(B2:B4,C2:C4,15) 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?