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?


1 Answer 1


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.
  • Does it matter if the lists are sorted or not?
    – Adam Matan
    Jun 11, 2018 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.