Create 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.mrexcel.com/forum/excel-...ml#post5014390

[leelnich]

Hi Drew- I believe this works:

COUNTIF(MATCH_VALUES_RANGE,DATA_RANGE)

If you have duplicates in the MATCH_VALUES_RANGE, you would get numbers other than 0/1 in the result_array. If that's a problem, use this instead:

--(COUNTIF(MATCH_VALUES_RANGE,DATA_RANGE)>0)

Obviously, array-entry (CSE) is required.

[drew.j.harrison]

Hmmm... the countif method doesn't seem to evaluate multiple matches properly. Ended up using the following from the cross posted thread .


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

[drew.j.harrison]

As you may have guessed I am using this to create a dynamic list of all the "release numbers" in a table that match specific "release types" identified by another dynamic range. Below is my actual code:

=IFERROR((INDEX(DATA_RELEASE_NUMBER,MATCH(0,COUNTIF(OFFSET(AI$2,0,0,ROW()-ROW(AI$2)),DATA_RELEASE_NUMBER)+(1-ISNUMBER(MATCH(DATA_RELEASE_TYPE,LIST_RELEASE_TYPES_TEMPORARY,0))),0))),"")

AI$2 is the header of the list of matching "release numbers" and the list generates a dynamic range called "LIST_UNSORTED_TR_NUMBERS"
DATA_RELEASE_TYPE is the dynamic "DATA_RANGE"
LIST_RELEASE_TYPES_TEMPORARY the dynamic "MATCH_VALUES_RANGE"

I am then using the following formula to sort the data Highest to Lowest to create a final dynamic range that is used for data validation, sorting, conditional formatting, etc. in various other places.

=IF(COUNTA(LIST_UNSORTED_TR_NUMBERS)>=ROWS(AJ$2:AJ3), INDEX(LIST_UNSORTED_TR_NUMBERS, MATCH(LARGE(COUNTIF(LIST_UNSORTED_TR_NUMBERS, "<"&LIST_UNSORTED_TR_NUMBERS), ROW()-ROW(AJ$2)), COUNTIF(LIST_UNSORTED_TR_NUMBERS, "<"&LIST_UNSORTED_TR_NUMBERS), 0)), "")

[FlameRetired]

If you want an array in memory to pass on to another calculation this array entered

=--NOT(MMULT(--(DATA_RANGE=TRANSPOSE(MATCH_VALUES_RANGE)),{1;1;1}))

will return {0;1;0;1;0}.

To output it to the spreadsheet will require this array entered and filled down.

=INDEX(--NOT(MMULT(--(DATA_RANGE=TRANSPOSE(MATCH_VALUES_RANGE)),{1;1;1})),ROWS(B$11:B11))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[leelnich]

I misread the requirements, had 0 and 1 switched. This would work:

--(COUNTIF(MATCH_VALUES_RANGE,DATA_RANGE)=0)