Google-sheets – How to check whether a range of cells is a subset of another range

google sheets

My Problem

I have a few lists on a Google Sheet, each kept in a column of cells. One of the list is the Master list, and I would like to make sure that the elements in the other lists are also present in master.

For example, list1 and list2 are valid, but list3 is invalid because x is not present in master.

Duplication is orthogonal to the question – I can use UNIQUE if I'd like to check for uniqueness as well.

What Have I Tried

LOOKUP only works for a single value and for sorted ranges
Using Google Sheets Query Language: Did not find any set / list operations

My Question

How can I check whether all the values in an unsorted cell range exist in another cell range?

Best Answer

LOOKUP only works for a single value

Not true.

=ARRAYFORMULA(AND(MATCH(I2:I4,H2:H5,0)))

MATCH to lookup the row of I2:I4 in the MasterList
AND to combine all matches and throw N/A,If any of the values are not found.
ARRAYFORMULA to use arrays in formulas.

Related Solutions

Google-sheets – How to check if value is in range of cells

You could use COUNTIF.

For example:

=COUNTIF(G:G,6) > 0

if you want to find the number 6 in the range G:G, and/or

=COUNTIF(A1:B7,"d") > 0

if you want to find d in the first seven rows of the first two columns.

COUNTIF will return the number of instances in its given range that are equal to its given value. It can also do a bit more complex conditioning (e.g all values higher than 6, or containing the letter A); see the docs for more info.

Google-sheets – Using query to find the entries in one column which are not in the other

This can be done with FILTER by MATCH:

=FILTER(C2:C240,ISERROR(MATCH(C2:C240,B2:B240,0)))

Here, MATCH returns an error if the element is not found in column B (the third argument, 0, requires exact match). The command ISERROR returns true in such a case, and this is what the column C is filtered on.

In SQL, this would be implemented using, e.g., select ... where not exists ... The query language available in Sheets is not powerful enough for that.