# Can Google Sheets have a discontiguous named range?

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?

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

• I'm afraid this completely eludes me. I'm not sure if it even applies... maybe you didn't quite read the question? Either that or I'm completely misunderstanding your answer. My goal is to have discontiguous cells in the same column, be part of a single named range. I don't see how that's possible with what you've outlined here. I even tried it just for fun, but Google Sheets will not accept notation as you've written here, for the range of a named range.
– JVC
Commented Oct 1, 2017 at 3:08
• Thanks, this really helped me. The answer doesn't address Named Ranges but rather, addresses a disjoint range specifier. I used this syntax to select for instance =FILTER( { Sheet!A:A, Sheet!C:C }, Sheet!B:B=false ) so that I do not have to deal with the B column in the results :) Commented Jul 6, 2019 at 14:51
• I tried this for three alternate columns and it didn't work. I tried to specify the range as {BA:BA,BC:BC,BE:BE}, but Google Sheets simply said, 'Invalid range'. I also tried adding the sheet's name to each column or outside the curly braces, but that didn't work either. Am I missing something here? Thanks for the good thoughts! Commented Aug 21, 2023 at 12:18

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.
• +1 for option 1 here. At minimum, you could put in some cell somewhere in another sheet the formula `={B1;B3}` and that would construct the range that you asked for. But it's more likely you'd do something like `=FILTER(A1:B3,A1:A3="ABC")` to dynamically generate the range you want from your data. Commented May 16, 2019 at 2:33
• @Jangari Thanks for your feedback. By the way, I just added a third alternative. Commented May 16, 2019 at 2:35

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.

• 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.

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.

``````     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.

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

For example:

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