Pls help in explaining a formula that is looking for a cell value in a column of comma separated values

[MtC]

Hello,

After reading a few threads, I found a formula that worked for what I needed however I am trying to see how I can explain it. If someone can help:

=IF(OR(ISNUMBER(SEARCH(","&A4&",",","&SUBSTITUTE(J:J," ","")&","))),"Yes","No")

 

[6StringJazzer]

It helps to work your way from the inside out. You can easily see how this works by going to the Formula panel in the ribbon and stepping through Evaluate. I would do it for you but you have shown a screen shot instead of actual data and I am not going to type all of your data in again.

SUBSTITUTE(J:J," ","")
This takes the first argument (J:J) and finds all occurrences of the second argument (a single space), and changes them to the third argument (the null string). That it, it removes all the spaces. Also it normally takes a single value as the first argument but here we have a whole column, so in Excel 365 this forces it to be interpreted as an array formula. So it returns an array of all the values in J:J with the spaces removed. We'll call this expression SU for now.

SEARCH(","&A4&",",","&SU&",")
This searches for the string in A4 adding a comma before and after it. It returns the position of the string it is looking for. So it is searching for the string
,1543465,
It is searching for this string in the values returned by S, adding a comma before and after. So it is looking at
,152486,
and so on. I would show the entire column but you have pasted a picture so I am not going to type all of your data in. Because S is an array, SEARCH will also return an array of numbers of where it finds the string. If it does not find the string, it will return a #VALUE! error instead of a number. We'll call this expression SE.

ISNUMBER(SE)
Because Se returns an array, this will return an array. The array value will be TRUE for values where Se has returned a number, indicating it found the value it is looking for. The array value will be FALSE when it returns the #VALUE error, because it's not a number. Let's call this IN.

OR(IN)
If any value in the array is TRUE, then OR will return TRUE. Otherwise it's FALSE. A value in the array is TRUE for any row where the number in A4 was found in column J.

=IF(OR(IN),"Yes","No")
If the number in A4 was found in column J, return "Yes", otherwise return "No".

 

[Cubist]

With 365, you can try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	IDS									Table
4	1543465		No							152486
5	152486		Yes							8579548, 6596878, 54525,1234
6	1234		Yes							454614, 56985
7	56985		Yes
8	12458		No
Sheet4

Cell Formulas
Range	Formula
C4:C8	C4	=IF(OR(--TEXTSPLIT(TEXTJOIN(",",,$J$4:$J$6),",")=A4),"Yes","No")

 

[HighAndWilder]

With 365, you can try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	IDS									Table
4	1543465		No							152486
5	152486		Yes							8579548, 6596878, 54525,1234
6	1234		Yes							454614, 56985
7	56985		Yes
8	12458		No
Sheet4

Cell Formulas
Range	Formula
C4:C8	C4	=IF(OR(--TEXTSPLIT(TEXTJOIN(",",,$J$4:$J$6),",")=A4),"Yes","No")

One question about this formula.

Why is there only one OR function logical test?

Thanks

 

[MtC]

It helps to work your way from the inside out. You can easily see how this works by going to the Formula panel in the ribbon and stepping through Evaluate. I would do it for you but you have shown a screen shot instead of actual data and I am not going to type all of your data in again.

SUBSTITUTE(J:J," ","")
This takes the first argument (J:J) and finds all occurrences of the second argument (a single space), and changes them to the third argument (the null string). That it, it removes all the spaces. Also it normally takes a single value as the first argument but here we have a whole column, so in Excel 365 this forces it to be interpreted as an array formula. So it returns an array of all the values in J:J with the spaces removed. We'll call this expression SU for now.

SEARCH(","&A4&",",","&SU&",")
This searches for the string in A4 adding a comma before and after it. It returns the position of the string it is looking for. So it is searching for the string
,1543465,
It is searching for this string in the values returned by S, adding a comma before and after. So it is looking at
,152486,
and so on. I would show the entire column but you have pasted a picture so I am not going to type all of your data in. Because S is an array, SEARCH will also return an array of numbers of where it finds the string. If it does not find the string, it will return a #VALUE! error instead of a number. We'll call this expression SE.

ISNUMBER(SE)
Because Se returns an array, this will return an array. The array value will be TRUE for values where Se has returned a number, indicating it found the value it is looking for. The array value will be FALSE when it returns the #VALUE error, because it's not a number. Let's call this IN.

OR(IN)
If any value in the array is TRUE, then OR will return TRUE. Otherwise it's FALSE. A value in the array is TRUE for any row where the number in A4 was found in column J.

=IF(OR(IN),"Yes","No")
If the number in A4 was found in column J, return "Yes", otherwise return "No".

Thank you sooo much Really appreciate this breakdown!

 

[MtC]

With 365, you can try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	IDS									Table
4	1543465		No							152486
5	152486		Yes							8579548, 6596878, 54525,1234
6	1234		Yes							454614, 56985
7	56985		Yes
8	12458		No
Sheet4

Cell Formulas
Range	Formula
C4:C8	C4	=IF(OR(--TEXTSPLIT(TEXTJOIN(",",,$J$4:$J$6),",")=A4),"Yes","No")

Hi

Thank you for your reply

I get a #CALC! error. I see the following:

The screen shot i provided before is just an example. The array is actually 11,818 in my actual file. So maybe that is why?

 

[HighAndWilder]

Hi

Thank you for your reply

I get a #CALC! error. I see the following:
View attachment 120100

The screen shot i provided before is just an example. The array is actually 11,818 in my actual file. So maybe that is why?

If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.

You probably get a #CALC! error because the calculation engine encounters an unspecified error with an array.

Providing all of the information up front is never a bad idea.

 

[Cubist]

Why is there only one OR function logical test?

Because there's only one array.

 

[RoryA]

Why is there only one OR function logical test

OR only ever returns one result. OR(array=value) will return TRUE if any element of the array equals the value, FALSE otherwise.

 

[6StringJazzer]

Why is there only one OR function logical test?

See my explanation where I discuss array formulas. It's a different formula but the explanation is the same. The argument to OR, once evaluated, is an array of TRUE and FALSE values. OR will return TRUE if at least of one those is TRUE, which tells you if the desired value is found anywhere in column J.