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

Question

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.

It looks like the easiest way to do this would be to define an array constant and then pass that into the lookup_value though I still can't get excel to iterate past the first value. — KJ Prince
– KJ Prince, Commented Aug 12, 2014 at 5:32

dotNET · Accepted Answer · 2014-08-12 05:31:06Z

2

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)

edited Aug 12, 2014 at 5:31

answered Aug 12, 2014 at 4:42

dotNET

35.7k29 gold badges176 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KJ Prince Over a year ago

I tried this and I'm getting the same result as my test. =MATCH(NamedValue, X3,0)

KJ Prince Over a year ago

I found this article as well: office.microsoft.com/en-us/excel-help/… But neither mentions passing in an array as the lookup_value

dotNET Over a year ago

I just checked and =MATCH("def", {"abc","def","ghi"}, 0) correctly returns 2 for me.

KJ Prince Over a year ago

My understanding is that the first parameter, in this case "def" is the lookup_value, which should be the array of values I want to return true for. Then the lookup_array should be the complete list of values, returning false for any string that isn't in the array.

parade · Accepted Answer · 2014-08-12 04:46:08Z

0

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.

answered Aug 12, 2014 at 4:46

parade

1063 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related