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

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

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 !

2. 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```

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•