Look if values exist seperately and together, then write Yes or No in cell

[MasterBash]

Hello,

Table exemple on Sheet1 :

1000	...	2317	1000 2317
1001	...	1187	1001 1187
1002	...	5996, 9666	1002 5996, 9666
1003	...	4862	1003 4862

Currently, I have this formula :

=IF(VLOOKUP(A2&" "&C2;'Sheet1'!D:D;1;FALSE)>0;"Yes";"No")

So it lookups value A2 and C2 in my current sheet, and if it is equal to column D on Sheet1, then Yes.

2 problems in the last 2 rows :
Third row : When there are 2 numbers in column C of sheet1. So 1002 5996, 9666 returns false even if both are available. Sometimes those numbers can be seperated by a comma, sometimes by a dash or other times, simply a space. Sometimes it is also like this : 1002 9666, 5996. It needs to work with 1 or more numbers, as long as those numbers are exact, regardless of the seperators.
Fourth row : By mistake, sometimes user enter extra spaces before/after the numberm resulting in a "No".

How can I modify my formula to make sure that those 2 cases are equal to Yes ?

Thank you !

[Paul_Hossler]

The way I read it was that the number(s) in Col A and the number(s) in Col C have to be in Col D

The data clean made me want to use a user defined function

Capture.JPG

Option Explicit


Function YesOrNo(x0 As Variant, x1 As Variant, x2 As Variant) As String
    Dim v0 As Variant, v1 As Variant, v2 As Variant
    Dim n As Long, i As Long, j As Long
    
    Application.Volatile
    
    YesOrNo = "No"
    
    If Len(x0) = 0 Or Len(x1) = 0 Or Len(x2) = 0 Then Exit Function
    
    v0 = pvtFixInput(x0)
    v1 = pvtFixInput(x1)
    v2 = pvtFixInput(x2)
    
    n = 0


    For i = LBound(v0) To UBound(v0)
        For j = LBound(v2) To UBound(v2)
            If v0(i) = v2(j) Then GoTo NextOne
        Next j
        
        Exit Function
NextOne:
    Next i
    
    For i = LBound(v1) To UBound(v1)
        For j = LBound(v2) To UBound(v2)
            If v1(i) = v2(j) Then GoTo NextTwo
        Next j
        
        Exit Function
NextTwo:
    Next i


    YesOrNo = "Yes"


End Function


Private Function pvtFixInput(v As Variant) As Variant
    Dim s1 As String
    Dim v1 As Variant
    Dim i As Long
    
    s1 = Trim(v)
    s1 = Replace(s1, " ", ",")
    s1 = Replace(s1, "-", ",")
    
    v1 = Split(s1, ",")
    
    For i = LBound(v1) To UBound(v1)
        v1(i) = Trim(v1(i))
    Next i


    pvtFixInput = v1


End Function

[MasterBash]

Yes, D is a helper column so I can compare on another sheet if the numbers match.

I am wondering if it possible to do it with a formula, instead of vba ? As this will be viewed online, where vba scripts dont run. Maybe my formula simply needs a small tweak.

Thank you.

[MasterBash]

Cross-posted here : https://www.mrexcel.com/board/thread...-cell.1261827/

No solutions at the moment. I will post an update if the issue is resolved.

[MasterBash]

Hello,

Bumping a thread that is over a month old.

Here is an updated workbook with the expected result :
number verification.xlsx

From the other thread, I found a solution for column B and D :

B formula for sheet2:

=IF(COUNTIF(List!A:A,A2),"Yes","No")

B formula for sheet3:

=IF(SUM(COUNTIF(List!A:A,TEXTSPLIT(Sheet3!A2,"-")))>0,"Yes","No")

D for sheet2 and sheet3:

=IF(SUM(--ISNUMBER(SEARCH(C2&", ",SUBSTITUTE(List!$C:$C,"-",", ")&", ")))>0,"Yes","No")

I have yet to find a solution for column E. Formula only, no VBA. Is this possible without VBA ?

[Aflatoon]

Not sure why you would post here of all places for a non-VBA solution, but anyway:

=IF(ISERROR(VLOOKUP(A2&"*"&C2&"*",List!D:D,1,FALSE)),"No","Yes")

[MasterBash]

I am aware of the other forums, but I very much like this one. I am aware this forum is mostly for vba, but I do not think there are any rules when it comes to asking questions about formulas. To me, it is just a preference.

Thank you for the formula, it works for sheet2. However, for Sheet3, the Yes/No are incorrect.

[Aflatoon]

For sheet3 you could use:
=IF(SUM(COUNTIFS(List!D,"*"&TEXTSPLIT(A2,"-")&"*"&C2&"*"))>0,"Yes","No")
although you should probably be using table refs given that you have tables.

[MasterBash]

Thank you so much. Agreed, I will be using table references. It may also help a bit with performance. Thank you.