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

#### drew.j.harrison

##### Board Regular
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.

Code:
``{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

Last edited:

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

##### MrExcel MVP
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)),"")

Last edited:

#### drew.j.harrison

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

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

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

Code:
``{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))}

Perfect. Thanks.

Replies
3
Views
853
Replies
2
Views
179
Replies
1
Views
357
Replies
18
Views
484
Replies
0
Views
192

1,136,909
Messages
5,678,516
Members
419,768
Latest member
eguechi09x

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back