Google-sheets – Can Google Sheets have a discontiguous named range

google sheetsgoogle-sheets-named-ranges

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?

Best Answer

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

Related Topic