Vlookup multiple numbers from string

[MHamid]

Hello,

I am not certain if this is possible or not, but would there be a way to perform a vlookup for multiple numbers (one at a time) against another sheet?
For instance, 06500- |All LVIDs|blah blah blah ...|blah blah blah;11111- |All LVIDs|blah blah blah ...|blah blah blah;11000- |All LVIDs|blah blah blah ...|blah blah blah
The numbers I need to search for in another file are always 5 digits long and are always before the dash(-).
The formula I am looking for will need to look at the cell with the text ("A1") and if one of these 5 digit numbers (06500, 11111, 11000) is in another sheet, then say "Yes". If not, then say "No".
Not all data will have multiple IDs like my example above.

Is this possible with a formula or would be it better with a macro?

Thank you

 

[DanteAmor]

Try the following UDF

Function LookupNumber(sText As String)
  Dim c As Variant, x As String, f As Range
  LookupNumber = "No"
  For Each c In Split(sText, "-")
    x = Right(c, 5)
    If Len(x) = 5 And IsNumeric(x) Then
      Set f = Sheets("Sheet1").Range("A:A").Find(x, , xlValues, xlWhole)
      If Not f Is Nothing Then LookupNumber = "Yes"
    End If
  Next
End Function

The UDF searches each number on sheet1 in column A

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LookupNumber just like it was a built-in Excel function. For example:

Dante Amor
	A	B
1	0600- |All LVIDs|blah blah blah ...|blah blah blah;11111- |All LVIDs|blah blah blah ...|blah blah blah;11000- |All	Yes
2
Sheet3

Cell Formulas
Range	Formula
B1	B1	=LookupNumber(A1)

 

[MHamid]

Hello,

The sheet that I need the formula for is a Shared EUC spreadsheet. Are there any restrictions when using UDFs or can anyone still use/see results for the UDF if they open the spreadsheet?

Thank you

 

[DanteAmor]

Did you try the udf with sheets from your book?

Is that what you need?
What do you mean with :

The sheet that I need the formula for is a Shared EUC spreadsheet. Are there any restrictions when using UDFs or can anyone still use/see results for the UDF if they open the spreadsheet?