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

31711





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/threads/look-if-values-exist-seperately-and-together-then-write-yes-or-no-in-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 :
31772

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: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.