Snasui Excel Forum

Hi dear !
I need any formula in E2:H6(expected result provided) to indicate if the number in E1:H1 is covered (included) in range description in column A (for the same row). display "yes" if included, "No" if not included

Example,"YES" in H2 means 311(H1) included in range description "310-312" in A2
Note highlight cells are expected results

Best Wishes,

The example formula in E2 is,

=IF(SUMPRODUCT(--(INT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A2,"-","."),",","</s><s>")&"</s></t>","//s"))<=E$1),--(--RIGHT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A2,"-",REPT(" ",10)),",","</s><s>")&"</s></t>","//s"),10)>=E$1)),"YES","NO")

Enter > Copy to the right side and down.

Thank you so much

Another solution,

E2 =IF(SUMPRODUCT(--(E$1>=TEXT(MID(SUBSTITUTE(SUBSTITUTE($A2,"-","."),",",REPT(" ",50)),50*(ROW($1:$50)-1)+1,50),"0;;;\0")+0),--(E$1<=TEXT(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",50)),50*(ROW($1:$50)-1)+1,50)),"-",REPT(" ",20)),20),"0;;;\0")+0)),"YES","NO")

Thank you both