excel formula to check if value in range

[radub]

Hi,
I am trying to figure out an excel 365 formula that can check if a number is in a list of numbers.
"list" is an excel column that contains both single values and intervals (e.g "80:81,441:443,500").
I was trying to use FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(" ",TRUE,D2),",","</y><y>")&"</y></x>","//y") to make a list into an array and then use ROW(INDIRECT(a:b)) to explode the a:b range
so far I came up with this one :
=SUM(--(IF(ISNUMBER(FIND(":",FILTERXML("<x><y>"&SUBSTITUTE(D2,",","</y><y>")&"</y></x>","//y"))),ROW(INDIRECT(FILTERXML("<x><y>"&SUBSTITUTE(D2,",","</y><y>")&"</y></x>","//y"))),NUMBERVALUE(FILTERXML("<x><y>"&SUBSTITUTE(D2,",","</y><y>")&"</y></x>","//y")))=H2))

but for some reason it can only see the first value in a range by using this formula.

any ideas are much apreciated
Thank you

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If H2 was 442 would that count as being in the range?
Also do you have VSTACK & TEXTSPLIT functions yet?

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C
1	Input	Split up	Check
2	80:82,441:444,500	80	442
3		81	In list
4		82
5		441
6		442
7		443
8		444
9		500
Sheet13

Cell Formulas
Range	Formula
B2:B9	B2	=LET(g,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1, L,TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),SEQUENCE(g)*999-998,999)), d,NOT(ISERR(SEARCH(":",L))), colon,SEARCH(":",L), s,IF(d,VALUE(LEFT(L,colon-1)),VALUE(L)), t,IF(d,VALUE(RIGHT(L,LEN(L)-colon)),VALUE(L)), r,t-s+1, m,MAX(r), a,MOD(SEQUENCE(ROWS(s),m,0),m), b,IF(s+a>t,s,s+a), rb,ROWS(b), cb,COLUMNS(b), ar,SEQUENCE(rb*cb), sa,INDEX(b,INT((ar-1)/cb)+1,MOD((ar-1),cb)+1), UNIQUE(sa))
C3	C3	=LET(g,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1, L,TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),SEQUENCE(g)*999-998,999)), d,NOT(ISERR(SEARCH(":",L))), colon,SEARCH(":",L), s,IF(d,VALUE(LEFT(L,colon-1)),VALUE(L)), t,IF(d,VALUE(RIGHT(L,LEN(L)-colon)),VALUE(L)), r,t-s+1, m,MAX(r), a,MOD(SEQUENCE(ROWS(s),m,0),m), b,IF(s+a>t,s,s+a), rb,ROWS(b), cb,COLUMNS(b), ar,SEQUENCE(rb*cb), sa,INDEX(b,INT((ar-1)/cb)+1,MOD((ar-1),cb)+1), IF(ISNA(MATCH(C2,sa,0)),"Not in list","In list"))
Dynamic array formulas.

 

[Fluff]

If you do have Vstack etc, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2				80:81,441:443,500		442		442
Main

Cell Formulas
Range	Formula
F2	F2	=LET(a,DROP(REDUCE("",TEXTSPLIT(D2,,","),LAMBDA(a,b,LET(x,TEXTSPLIT(b,":",,1),VSTACK(a,IF(COLUMNS(x)>1,SEQUENCE(INDEX(x,1,2)-INDEX(x,1,1)+1,,INDEX(x,1,1)),x+0))))),1),FILTER(a,a=H2,0))

 

[radub]

Thany you so much guys!
@Fluff - unfortunately i don't have the insider version
@James - you solution does the trick.
i wasn't aware of this kind of power that LET function has.
Thank you once again.

 

[Fluff]

unfortunately i don't have the insider version

Those functions are now on general release for people on the monthly channel, so it maybe that you are on the semi-annual channel.