Data Validation of a cell with multiple values

[PicklePinata]

Hi everyone,
I hope someone here can help me. I've poured through posts to no avail.

So I have a table with values (initials of people) in a control sheet.
I want to be able to have a cell verify if the initials that are being inputted are on that table. It sounds like a standard Data validation question but the kicker is that each cell can have more than one initial (Separated by commas).

Example:
Cell D2 contains "XX1, XX2, XX6"

XX1 and XX2 are on the list of approved initials, but XX6 is not. I want an error to pop up saying that XX6 is not a valid initial.

Any help on this would be super appreciated!

 

[FDibbins]

It would probably be better if you could restrict initials to just 1 per cell. Or, if that is not possible, you could use a formula/s in helper columns to break the initials out, then test them?

 

[PicklePinata]

Hmm, that's unfortunate. I suppose I could try to get them to restrict initials to 1 per cell.

 

[FDibbins]

What would the max number of initials be?