Google Sheets – Formula to Select a Range Excluding a Sub-Range

formulasgoogle sheets

I want to say A1:A60 excluding A20:A30. Is there a minus operator for use with ranges, in Google Sheets?

I know that you can take the union of two ranges, like {A1:A19;A31:A60}. I would like to know if there's an operator that excludes one range from another.

Best Answer

With the following formula it is possible to exclude a certain range:

=ARRAYFORMULA(QUERY({ROW(A1:A60), A1:A60}, "SELECT Col2 WHERE Col1<20 OR Col1>30"))

Related Solutions

Google-sheets – How to select Column1’s value if an input value is between the integers in Columns2:3 for a range

This will work. I've used the Named Ranges, Name and From. You don't have to use To at all, since your values are all contiguous. B2 is what I used as the value of the first BMI, you can just drag this formula down the column:

=IFERROR(INDEX(Name, MATCH(B2,From),1),INDEX(Name,1,1))

Google Sheets – Reference Elements in Multi-Column Range by Condition

This is exactly the job of the QUERY command:

=QUERY(Sheet1!D:F, "select D,E where F='Case'")

returns

Sinclair    Complete
Davis       Complete

Best Answer

Related Solutions

Google-sheets – How to select Column1’s value if an input value is between the integers in Columns2:3 for a range

Google Sheets – Reference Elements in Multi-Column Range by Condition

Related Topic