Can Google Sheets have a discontiguous named range?

Question

I have a data set with multiple values that are not adjacent. I wish to make a named range of these values.

Simplified example data:

     A      B
1    ABC    536936062976506
2    XYZ    891048446616931
3    ABC    250538612742584

I wish for B1 and B3 to be part of a single named range, but I can't find a way to do this. Is it possible?

Answer 1

Sure you can! To define non-contiguous ranges, use an array notation: on the box where you define a range enter:

 {A:A,C:C,E:E} 

This should create a discontinuous range that includes columns A, C and E but not B or D.

In functions like vlookup the named range behaves as if B and D did not exist and A,C and E were next to each other, in other words, a VLOOKUP(value, named range, 2) will select cells belonging to C (it's the logical range's index2.

Selecting like so only works for columns of equal height: can't have a "jagged " non-contiguous range where some columns are shorter.

It's also possible to do so on equally long rows, but that requires a two dimensional array:

 {{b5:5}, {b8:8}, {b10:10}}

Would create a non-contiguous range including rows 5, 8 and 10 from column B onwards, and

 {{b5:h5}, {b8:h8}, {b10:h10}}

Would include 3 rows, non-adjacent to each other 6 columns wide, starting in column b and ending in column H

Again, can't have jagged rows, (starting or ending on different columns even if they total 6 cols per row

Answer 2

The built-in named range feature doesn't support non-adjacent ranges but there are some alternatives.

1. Use an auxiliary sheet to put together the values of non-adjacent ranges, so we have them together. We could use QUERY, FILTER, use {}notation, etc.
2. Use Google Apps Script to create custom function to be used as named ranges.
   • NOTE: The "problem" with custom functions is that they only recalculate when their parameters change and that can't use volatile functions, like NOW() as arguments.
3. (UPDATE) Another alternative is to create an array of range references and use the Google Apps Script methods to handle range lists.

Answer 3

At this point, I don't think it's possible. So, what to do?

If it's simple, you can just do this:

C1=B1
C2=B3

then create a named range for C1:C2

This is hard to maintain.

A trick I've done to make this easier to maintain.

• Add a range name column to your source data.
• In an auxiliary sheet filter the source data by that text in that column
• Use that sheet for your named range.

If you use the right filtering technique (regexmatch...) you can create multiple named ranges from a single data table.

Answer 4

I don't know if you can create a named range, but you might be able to treat the data like a named range using filter and regexmatch functions together.

So given your data

     A      B
1    ABC    536936062976506
2    XYZ    891048446616931
3    ABC    250538612742584
4    XYZ    730573819217927

Something like this

=slope(filter(B1:B4,REGEXMATCH(A1:A4,"ABC")),filter(B1:B4,REGEXMATCH(A1:A4,"XYZ")))

Would allow you to call your data using the ABC and XYZ names.

Answer 5

You might try to add {} brackets around a noncontiguous range in a formula.

For example:

=COUNTIF({A3,C3,E3,G3,I3}, true)