How to skip columns in “List from a range” Criteria?

Question

Is it possible to create a "List from a range" Data Validation rule in Google Sheets where the range skips columns?

For example:

Cells A6:A11 is limited to the range A1:B3. Cells B6:B11 is limited to the range A1:A3 AND C1:C3 (skips column B).

Creating a Data Validation rule for cells A6:A11 is trivial as I simply need to create a Criteria of "List from a range = A1:B3".

However, creating the Data Validation rule for cells B6:B11 is not so intuitive since Google Sheets does not allow me to create a Criteria using the syntax "List from a range = A1:A3, C1:C3".

Does the "List from a range" Criteria support a syntax that allows us to skip columns within a range?

Note: I currently have a work around for this where I defined an array formula in D1 = =ArrayFormula(if({1,""},A1:A3,C1:C3)) and then use D1:E3 as the Data Validation range. But this is a hacky solution and I'm hoping there is a better way to accomplish my goal.

Answer 1

The solution is to use { } to create a combination of columns or rows that will result in some sort of virtual table on-the-fly.

Example:

Assuming you have a spreadsheet with Name, Age, Gender, Phone and Address in A, B, C, D and E, and you want to skip the Gender (column C) while using the UNIQUE statement, you can use something like this.

Put in G1 the following formula:

=UNIQUE({A1:B, D1:E})

From the cell G1, the spreadsheet will populate the columns G, H, I and J with unique combinations of A, B, D and E, excluding the column C (Gender).

The same application of a combined range can be used in any formula and also you can combine multiple different ranges, including cross Spreadsheets and Files.

It is a very useful trick if you need to combine pieces of multiple spreadsheets for data visualization or reports. However, always remember you cannot manipulate the displayed data. You can still search through it, format it, etc., but you cannot change it. On the other hand, it will auto-update always if the data source gets updated, which is very useful.

Note: Try it with LOOKUP, VLOOPUP or HLOOKUP.