google sheets
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 );
This can be done by invoking match inside arrayformula, as follows:
=arrayformula(if(len(A12:A), if(isna(match(A12:A, B12:B, 0)), "Don't remove", "Remove"), ))
or with linebreaks,
=arrayformula(
if(len(A12:A),
if(isna(match(A12:A, B12:B, 0)),
"Don't remove", "Remove"
), ))
The combination if(isna(match( means "if not found", literally "if match returned #N/A error". The outer if takes care of empty cells. Note that the trailing comma is intentional: this is how one correctly leaves a cell blank based on IF statement.
If you also make the argument a range, also the cells your describing in the question do not match the sample sheet you shared - based on the sheet and the cells it is referring to, this is the formula that works:
=IFERROR(ARRAYFORMULA(FIND(B12:B18, B3:B9)))
IFERROR basically avoids showing #N/A when the value is not yet found, and since you want to apply the find function to the list of names you have written you need to pass it a range of names to look up, completed by wrapping it with ARRAYFORMULA .
If you share a more complete sheet that is closer to the actual results your trying to get it would be easier to come up with a more dynamic answer, likely a simpler way to do it also .
To match in any order you should move your search list to another column or sheet , pretend you move your list of names to start in cell I2 you would update your formula and enter it in H2:
=IFERROR(ARRAYFORMULA(FIND(H2:H, B2:B)))
Best Answer
I am not sure of your cell references and
Get a list of items missing from arrayseems quite different fromI'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
elephantand B5bannanaare showing a red warning triangle and a warning message when hovered over.Change from
Show warningtoReject inputthen trying to enter something likexxxin B7 should result in a windowOops The data that you entered in cell B7 violates validation rules set on this cell.andxxxwill not be accepted in B7.