ISNA and VLOOKUP from CSV LIST

[mikesig7]

Hello -

I am trying to write a formula to validate entries on a spreadsheet based on a comma separated list of "keys". The goal is to create an error message that says "ERROR: The following items were not approved entries [Horse, Chicken]". If there is only one item in C5, the formula works, but I cannot figure out how to convert the csv list to something that vlookup will accept.

Any help would be greatly appreciated!

HEllo

 

[DRSteele]

Welcome to the forum.

You must split cell C3 with the comma and space into an array. Like this:

MrExcel posts20.xlsx
	C	D
3	Dog, Cat, Monkey
4
5		Horse
6		Chicken
7	Dog
8	Dog
9	Cat
10	Horse
11	Chicken
12	Monkey
Sheet9

Cell Formulas
Range	Formula
D5:D6	D5	=FILTER(C7:C12,ISNA(MATCH(C7:C12,TRANSPOSE(TEXTSPLIT(C3,", ")),0)))
Dynamic array formulas.

 

[mikesig7]

Thank you!

 

[DRSteele]

You're welcome.