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

[MasterBash]

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

Hello, Table exemple on Sheet1 : 1000 ... 2317 1000 2317

www.vbaexpress.com

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 ?
Unfortunately, VBA is not an option.
Thank you !

 

[MasterBash]

And I also imagine that the data from sheets 2 and 3 can be on the same sheet, something like this, @MasterBash or am I wrong?

Sheet2 and Sheet3 will never share data, because those 2 sheets are different clients. An order can't go to 2 different clients.

that way the order and number can exist but not necessarily on the same row and the search should verify that they are on the same row.

That is correct, both Order and number need to be on the same row to be an Exact match.

 

[MasterBash]

Thanks to Cubist, column B and D are accurate with the formula.

However, I am still looking for an answer to column E on Sheet2/Sheet3. Formula only, no VBA. Column E verifies that column A and C values in Sheet2/Sheet3 are in the same row in Sheet1.

I am not sure if this is even possible with a formula ?