Get a list of items missing from array

Question

I have a spreadsheet where I'm using the trailing set of columns as a kind of foreign key; I'm trying to throw an error if someone enters a value that doesn't exist.

I'm thinking one way to do this would be to treat the remaining columns as an array (ie: I2:2) and compare that array to the list of all names (ie: A2:A).

Is there a way to do this?

If their query capability was more sophisticated I could do something like:

SELECT I FROM I2:2 WHERE NOT EXISTS( SELECT 1 FROM A2:A WHERE A=I );

Answer 1

I am not sure of your cell references and Get a list of items missing from array seems quite different from I'm trying to throw an error if someone enters a value that doesn't exist. but considering the last, Data Validation seems to be suitable. For example:

uses ColumnA as a list of valid strings (fruits) against which entries in a range of choice are validated. That range is B1:I20 in the example so B3 elephant and B5 bannana are showing a red warning triangle and a warning message when hovered over.

Change from Show warning to Reject input then trying to enter something like xxxin B7 should result in a window Oops The data that you entered in cell B7 violates validation rules set on this cell. and xxx will not be accepted in B7.