Excel validate entries in list of lists

I have a master list of things (column A below), and I have a list of lists (column C below), where all sub-entries should exist in the master list. I would like to validate the list of lists (in column D). For example, the below screenshot shows a mock-up example of the data and the expected result in column D:

https://www.dropbox.com/scl/fi/lqknilfbkfn5d52o5covs/Check-entries-in-lists-in-list-are-all-valid.xlsx?rlkey=ze5lmwp9bf2vh00b3l85yneew&st=vl6r6cdr&dl=0

What formula could I use for column D?

>Solution :

Here is one way of accomplishing the desired output using XMATCH() + TEXTSPLIT() + ISNUMBER() + AND() funtcions:

• Formula used in cell D2

=AND(ISNUMBER(XMATCH(TEXTSPLIT([@[List of lists]],","),Table1[Master List of Things])))

• Or bit shorter using 1-ISNA() instead of ISNUMBER() which does the same operation here:

=AND(1-ISNA(XMATCH(TEXTSPLIT([@[List of lists]],","),Table1[Master List of Things])))