Help - lookup range with cell containing multiple values

[chongli]

Hi All,

I've run into a problem that I'm struggling to solve alone. With reference to the attached picture, column S contains billing codes that cannot be used in conjunction with their corresponding value in column C.
The aim is to have this instance flagged if column C contains any of the values in column S - as per highlighted row 12

The problem specifically relates to row 15 which has several corresponding items in column S for that billing code.

How am I able to search multiple values in S15 to match against the values in column C?

I hope this description makes sense. Let me know if you need a further breakdown. Any help is appreciated.

 

[shinigamilight]

I don't have excel 365 but you can try this.

=IF(SUM(IFERROR(SEARCH(TEXTSPLIT(SUBSTITUTE(S12," ",""),,","),C12),0))=0,"no match","match found")

 

[chongli]

I slightly amended the formula to match the range in column C. However, it only works for single values in column S. Still doesn't appear to be splitting multiple values in S15 by comma to search.

=IF(SUM(IFERROR(SEARCH(TEXTSPLIT(S15,,","),[B]$C$12:$C$26[/B]),0))=0,"no match","match found")

 

[chongli]

Actually, just realised the formula does work but only if the values in S15 are entered above C15 and not below. Any idea why this might be happening?

=IF(SUM(IFERROR(SEARCH(TEXTSPLIT(SUBSTITUTE(S15," ",""),,","),$C$12:$C$26),0))=0,"no match","match found")

 

[chongli]

Apologies for spamming the thread but unable to see edit.

Upon further testing, it appears that S15 is only identifying MR240 when entered in column C and none of the other values from S15

 

[shinigamilight]

It will work on one cell at a time. Why are u adding the range in it? Didn't you want to search the values of s12 in c12 and so on? I tested it and it was working fine? Just enter it one of the column and drag it down. Maybe you didn't explain what you want properly.

 

[chongli]

It will work on one cell at a time. Why are u adding the range in it? Didn't you want to search the values of s12 in c12 and so on? I tested it and it was working fine? Just enter it one of the column and drag it down. Maybe you didn't explain what you want properly.

Ok, what I need is for it to search the value(s) in S12 against the range in c12-c26 for their presence to confirm a "match found". If the value(s) in S12 are not in C12-26 then "no match".

I previously used guiding cells and XLOOKUP to match for single items, but this failed to identify multiple values in a single cell of column S

The formula you provided will only search against the single line which will always be "no match" in my case because the item(s) in column S that correspond with C are bound on another referenced sheet. I've attached a picture of the reference sheet, so hopefully this helps to understand why that's the case. I appreciate your assistance with this.

 

[shinigamilight]

Okay if this doesn't do it idk.

=IF(SUMPRODUCT(COUNTIF($C$12:$C$26,TEXTSPLIT(SUBSTITUTE(S12," ",""),,",",1)))=0,"No match","Match found")

 

[chongli]

Okay if this doesn't do it idk.

=IF(SUMPRODUCT(COUNTIF($C$12:$C$26,TEXTSPLIT(SUBSTITUTE(S12," ",""),,",",1)))=0,"No match","Match found")

Tested and works perfect. Thank you!