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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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,141,298
Messages
5,705,569
Members
421,399
Latest member
hjweiss00

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