Return array of matches in Excel-CodePudding

I'm trying to get Excel to return an array of 1s and 0s for a column depending on whether it finds a match in another look-up column. For example, my column might be

and my look-up column might be

and I'm looking for Excel to return the array {1, 1, 0, 0, 1, 1} to use this in a SUMPRODUCT involving several of these look-up arrays. Any help is appreciated.

CodePudding user response：

Evaluating Formulas `F9`

Formula                             Evaluation

=MATCH(A1:A6,B1:B2,0)               ={1;2;#N/A;#N/A;1;2}
=ISNUMBER(MATCH(A1:A6,B1:B2,0))     ={TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}
=--ISNUMBER(MATCH(A1:A6,B1:B2,0))   ={1;1;0;0;1;1} 

The Opposite
 
=--ISERROR(MATCH(A1:A6,B1:B2,0))   ={0;0;1;1;0;0}

CodePudding user response：

With Microsoft-365 can try-

=XLOOKUP(A1:A6,B1:B2,--(B1:B2<>""),0)

Evaluating Formulas F9

Evaluating Formulas `F9`