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

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jul 9, 2010
Messages
65
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]
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))}
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top