Using SUMPRODUCT and ISNUMBER to find if an array of number has a number included with special conditions

[Lekazard]

I'm banging my head with this issue and help would be highly appreciated.
I have some data gathered monthly and to sum it up I need to discover if midweek holidays are included within a range of dates. For simplicity the relevant data on the sheet is structured like this:

D: Dates I'm comparing	H: Date range	I: Expected output values
44536	44531; 44532; 44533; 44537; 44538; 44539; 44540	FALSE
44556	44531; 44532; 44533; 44537; 44538; 44539; 44540; 44550; 44551; 44552; 44553	FALSE
44559	44531; 44532; 44533; 44537; 44538; 44539; 44540; 44550; 44551; 44552; 44553; 44557; 44558; 44559; 44560; 44561	TRUE

What I want to know is does the the array listed in column H have any of the numbers listed in column D with the special rule there must be one consecutive datevalue before and 2 after (to simplify, if the number we are comparing is 2, then an array of 1, 2, 3, 4 would give TRUE).
The two formulas I've tried to use in column I are:
=IF(AND(ISNUMBER(MATCH(D2-1; $H$2:$H$1000; 0)); OR(ISNUMBER(MATCH(D2+2; $H$2:$H$1000; 0)); ISNUMBER(MATCH(D2+3; $H$2:$H$1000; 0)))); TRUE; FALSE)
and
=IF(SUMPRODUCT(--(ISNUMBER(MATCH(D2:D1000-1; $H$2:$H$1000; 0))); --(ISNUMBER(MATCH(D2:D1000+2; $H$2:$H$1000; 0))); --(ISNUMBER(MATCH(D2:D1000+3; $H$2:$H$1000; 0)))) > 0; TRUE; FALSE)
but both give FAIL for every row meaning, that they are not compiled right. Because of localization I use semicolon as the separator inside formulas. Could anyone help me out with this one because my head is really starting to hurt.

 

[kvsrinivasamurthy]

In I2 copy down

=ISNUMBER(FIND("; "&$D2&"; ","; "&$H2&"; "))

 

[Georgiboy]

Maybe:

Book1
	A	B	C
2	44536	44531; 44532; 44533; 44537; 44538; 44539; 44540	FALSE
3	44556	44531; 44532; 44533; 44537; 44538; 44539; 44540; 44550; 44551; 44552; 44553	FALSE
4	44559	44531; 44532; 44533; 44537; 44538; 44539; 44540; 44550; 44551; 44552; 44553; 44557; 44558; 44559; 44560; 44561	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(COUNTA(LET(ts,--TEXTSPLIT(B2,";"),FILTER(ts,(ts>=A2-1)*(ts<=A2+2))))<4,FALSE,TRUE)

 

[Fluff]

For 365 how about

Fluff.xlsm
	D				H	I
1
2	44536				44531; 44532; 44533; 44537; 44538; 44539; 44540	FALSE
3	44556				44531; 44532; 44533; 44537; 44538; 44539; 44540; 44550; 44551; 44552; 44553	FALSE
4	44559				44531; 44532; 44533; 44537; 44538; 44539; 44540; 44550; 44551; 44552; 44553; 44557; 44558; 44559; 44560; 44561	TRUE
Master

Cell Formulas
Range	Formula
I2:I4	I2	=SUM(--(ISNUMBER(MATCH(SEQUENCE(4,,D2-1),TEXTSPLIT(H2,";")+0,0))))=4

 

[Lekazard]

Big thanks for trying to help me. I tried both methods: =ISNUMBER(FIND("; "&$D2&"; ","; "&$H2&"; ")) and =IF(COUNTA(LET(ts,--TEXTSPLIT(B2,";"),FILTER(ts,(ts>=A2-1)*(ts<=A2+2))))<4,FALSE,TRUE), but for every row it unfortunately gives the result FAIL.

Could there be something wrong with either the settings and localization or the way the data is formatted in my sheet?

 

[Fluff]

Did you try the formula I suggested?

 

[Georgiboy]

Did you swap the , for ; in the formulae you tried?

Depending on language sometimes the function names can also be different (I think)

 

[Lekazard]

Did you try the formula I suggested?

I did. Thanks for trying to help, but for every row it only returns FAIL

Did you swap the , for ; in the formulae you tried?

Depending on language sometimes the function names can also be different (I think)

Yes, in my localized Excel semicolon is the separator and for every formula I try I always replace commas into semicolons because otherwise it only gives the error popup.

 

[Fluff]

If you put

=len(d4)

into a cell what does it return?

 

[Lekazard]

If you put

=len(d4)

into a cell what does it return?

5