MasterBash
New Member
- Joined
- Jan 22, 2022
- Messages
- 47
- Office Version
- 365
- 2021
- Platform
- Windows
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 :
Excel Formula:
=IF(VLOOKUP(A2&" "&C2;'Sheet1'!D:D;1;FALSE)>0;"Yes";"No")
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 !