Google-sheets – Define a range for one column by matching values in another

google sheets

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

e.g.,

=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?

Best Answer

"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)

Related Solutions

Google-sheets – Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

Don't use several onEdit-functions. Places place both IF-statements in the same tag.

Also, you shouldn't have semicolons after the IF-statements brackets.

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "General Meeting Attendance Roster" ) { //checks that we're on the correct sheet
       s.getRange('S6').setValue("Roster last updated: " + (new Date()));
  }

 if( s.getName() == "Master Chapter Roster" ) { //checks that we're on the correct sheet
       s.getRange('K1').setValue("Chapter Roster last updated: " + (new Date()));
  }
}

Google-sheets – Dynamically Building Array Of Sheet Names From A Range Of Cells

Without any custom function (Google Apps Script) the only thing you could do is have a sheet where you list them all out and reference them in that list. Basically manually make the list and have your formula reference the cells listing the sheet names.

If you want to go the route of a custom function (I see you are saying without, but I have to be complete...) see the thread Display Sheet Name in Google Spreadsheet. You will need to use the answers which tell you to create a custom function. It will require a modification to loop through to each sheet, though.

Explanation of doing the option in the first paragraph:

The INDIRECT() function Returns a cell reference specified by a string. So we can use that to combine a fixed range and the name of the sheet from a cell. Placing the Sheet names in row A, and this formula in B1, you will get the contents of the sheet named in cell A2, rows A and B from the first row to the last:

=INDIRECT(A2 & "!A1:B")

Making an array of the calls and applying this to a query(), this will return all items in the sheets listed in cells A1 and A2:

=query({ INDIRECT(A1 & "!A1:B"); INDIRECT(A2 & "!A1:B")}, "select * where Col2 <> ''")

The challenge is getting all the items in row A to create the array you are referencing. If you have the ability to write javascript, I would create a custom function. Since you said you could do a new file each year, you can add additional ; INDIRECT(A2 & "!A1:B") before the closing brace } and increment the A2 to A3, A4, etc...

I tried arrayformulas to get multiple INDIRECT() calls and a few other things but was not finding a good solution.

Best Answer

Related Solutions

Google-sheets – Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

Google-sheets – Dynamically Building Array Of Sheet Names From A Range Of Cells

Related Topic