CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Ken Prince
Ken Prince

Reputation: 1496

Excel - Find Strings in Column That Do Not Equal Array of Values

I have a column of data. I want to find all the values that do not equal any values from an array, one by one.

I tried defining a named value which was an array like this

={"value1","value2","value3","value4","value5"}

..and so on.

Which excel functions can I use to find values that are not contained in the array.

And why is the function not iterating through the array? It's only testing the first index.

I tried this: {=NOT(X8=NamedValue)}

And =MATCH(NamedValue, X3,0)

Both only test the first value in the array.

Upvotes: 0

Views: 9199

Answers (2)

dotNET
dotNET

Reputation: 35430

You should use Excel's MATCH() function for this purpose. Supply 0 to the match_type parameter. More information can be found on this link.

Examples of usage:

Pass hard-coded list of values to the formula:

=MATCH("dfs", {"erw","sad","dfs"}, 0)

Pass cell range to the formula:

=MATCH("dfs", C1:C15, 0)

Upvotes: 2

parade
parade

Reputation: 106

Assume column A is your list of "good values", column B is list of all values.

A B value2 value1 value3 value2 value4 value3 value4 value5 Place the following formula in C1, and copy down

=IF(ISNA(VLOOKUP(B1,$A$1:$B$5,2,FALSE)),0,1)

Which will place a "1" in the the rows where there is a match, and false otherwise.

Upvotes: 0

Related Questions