Excel Compare Comma delimited cell with single cell 3

[ITALIAORIANA]

Hi, I have a list of codes in one cell separated by comma's. I have to compare another cell to see if that value exists. I've tried using the Vlookup formula but it doesn't work. I need to compare BPL to PROC to make sure the BPL codes are in the PROC column.

[pre]
PROC BPL
AAC, AK, F2, FFW AK
DAC, RK, F2, CCF C2
[/pre]

Thanks
Deana

 

[mintjulep]

FIND()

 

[Andrzejek]

FIND() formula will work for you to find AK in AAC, AK, F2, FFW, but will give you #VALUE! when looking for C2 in DAC, RK, F2, CCF (not found))

FIND() will also find [red]this[/red]:[pre]
PROC BPL
AAC, Y[red]AK[/red]X, F2, FFW [red]AK[/red][/pre]

Is that what you want

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[ITALIAORIANA]

Hi Andrzejek, no I need an exact match on the BPL code. It would be nice to have "found", "not found" but not imperative.

 

[Andrzejek]

Does it have to be case sensitive? Is AbC 'found' or 'not found' in aBc?
Are you open to a VBA solution (UDF)?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[ITALIAORIANA]

Case is not important for this report, everything is in all caps. Yep, I am open to any help.

 

[mintjulep]

Good catch Andy.

There might be a formula approach starting point hear

https://www.excelforum.com/excel-fo...se-the-find-function-in-an-array-formula.html

 

[Andrzejek]

Add a standard Module to your Excel, paste this code:
(modified code from thread707-1813003)

Option Explicit

Function ITALIAORIANA(ByRef strPROC As String, strBPL As String) As String
Dim ary() As String
Dim i As Long
Dim b As Boolean

ary = Split(strPROC, ",")
For i = LBound(ary) To UBound(ary)
    If Trim(ary(i)) = Trim(strBPL) Then
        b = True
        Exit For
    End If
Next i

If b Then
    ITALIAORIANA = "Found"
Else
    ITALIAORIANA = "Not Found"
End If

End Function

and use this Function in your worksheet:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[ITALIAORIANA]

Thank you so much for this!

 

[Andrzejek]

You are welcome
I hope you understand how this code (and your Function) works and will be able to create your own Functions / formulas, if needed. If you have any questions about it - just ask.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[ITALIAORIANA]

I understand enough to get by for now, but I have a lot of reading to do and testing other things out. I know it's for work but I find this kind of stuff super fun. (My co-workers think I'm a weirdo for that LOL) I appreciate the help and the tips on where to find explanations on the functions. I am learning as I go so when I google things I don't always have the correct terminology which makes it difficult to find answers.

 

[mintjulep]

Not thoroughly tested, but seems to work.

=MATCH(C1,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(A1,",","</y><y>")&"</y></x>","//y")),0)

Where the comma delimited string is in A1 and the characters to test for are in C1.

Credit and how it works:

https://exceljet.net/formula/text-split-to-array

 

[strongm]

OR

[tt]=IF(ISERROR(SEARCH(B2 & ",",A2 & ",")), "Not found","Found")[/tt]