Create a an array for matches of a source array to any values in a match array.

[drew.j.harrison]

I'm looking to identify all rows in a range that match any values from another dynamic range that can have a varying number match values. Need to get all matches to remain 0 and all non matches to go to 1. Below is the current code I am using but it does not evaluate it as single array as I was hoping.

{IF(DATA_RANGE=TRANSPOSE(MATCH_VALUES_RANGE),0,1)}

Below is an example of what the two input ranges would be and the output I am looking for. Thanks for the help.

MATCH_VALUES_RANGE
1
2
3

DATA_RANGE
1
7
2
9
3

DESIRED ARRAY RESULT
0
1
0
1
0

CROSS POST
https://www.excelforum.com/excel-ge...-any-values-in-a-match-array.html#post4849659

 

[Aladin Akyurek]

Method A:

In E1 enter the following formula:

=ISNUMBER(MATCH(DATA_RANGE,MATCH_VALUES_RANGE,0))+0

Now select E1:E5 and apply control+shift+enter.

Method B.

In F1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(ISNUMBER(MATCH(DATA_RANGE,MATCH_VALUES_RANGE,0))+0,ROWS($F$1:F1)),"")

 

[drew.j.harrison]

Just needed to flip the 1s to 0s with an if statement. Below is what I am using now. Thanks!

{IF(([COLOR=#333333]ISNUMBER(MATCH(DATA_RANGE,MATCH_VALUES_RANGE,0))+0)=1,0,1)[/COLOR]

 

[Aladin Akyurek]

Just needed to flip the 1s to 0s with an if statement. Below is what I am using now. Thanks!

{IF(([COLOR=#333333]ISNUMBER(MATCH(DATA_RANGE,MATCH_VALUES_RANGE,0))+0)=1,0,1)[/COLOR]

We don't need any IF...

{=1-ISNUMBER(MATCH(DATA_RANGE,MATCH_VALUES_RANGE,0))}

 

[drew.j.harrison]

Perfect. Thanks.

 

[Aladin Akyurek]

Perfect. Thanks.

You are welcome.