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.

enter image description here

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

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.

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