Excel function to find values within cells of CSV-CodePudding

I have a Microsoft Excel (2204 Build 15128.20280) sheet.

Column G contains a comma separated list of values (single digit). I would like to have Excel function tell me which cells in column G contain a value in B1:E1.

	A	B	C	D	E	G
1	1	2	3	4	5
2						1
3						1,2
4						3,4
5						1,5

I can come close with this function: =NOT(ISERROR(SEARCH(B1:E1,G2:G5)))

	2	3	4	5
1	FALSE	FALSE	FALSE	FALSE
1,2	TRUE	FALSE	FALSE	FALSE
3,4	FALSE	TRUE	TRUE	FALSE
1,5	FALSE	FALSE	FALSE	TRUE

What I actually want is a 1-dimensional array that is the OR of each row above. I would prefer not to use a VB solution.

	2,3,4,5
1	FALSE
1,2	TRUE
3,4	TRUE
1,5	TRUE

This says:

G2 does not contain a value in B1:E1 1 ∉ {2, 3, 4, 5}
G3 contains a value in B1:E1 3 ∈ {2, 3, 4, 5}
G4 contains a value in B1:E1 3, 4 ∈ {2, 3, 4, 5}
G5 contains a value in B1:E1 5 ∈ {2, 3, 4, 5}

CodePudding user response：

Use BYROW and LAMBDA:

=BYROW(G2:G5,LAMBDA(a,SUM(--(ISNUMBER(SEARCH(B1:E1,a))))>0))

CodePudding user response：

I finally was able to resolve this with some matrix multiplication. I create a non-zero matrix with the same number of columns as my horizontal list of values.

=LET(
    future_periods,
    $B$1:$E$1,

    period_csv,
    $G$2:$G$5,

    in_future_periods_int,
    NOT(ISERROR(FIND(future_periods, period_csv))),

    in_future_periods,
    MMULT(--in_future_periods_int, SEQUENCE(COLUMNS(in_future_periods_int)))>0,

    in_future_periods
 )