Select Case function to return multiple values against multiple ranges

[Matei]

I'm trying to create a formula/addin in vba which is basically a vlookup to return a company name against company numbers.




In a typical vlookup I will need to have a seperate table of information to vlookup against, but I need to use this function across multiple sheets at various times and having a long list of information always open isn't really an option




to make things more difficult, the company number could be in various cells.


the below is what I have so far but i've got stuck when it comes to adding multiple cells as variants.


if I do "IslikeNumber(A1)" it will return the correct result, but I need something which allows for multiple, optional cells "=IsLikeNumber(A1, B1, F1, Ax1)" etc.

Function IslikeNumber(inValue As Variant, Optional inValue2 As String, Optional InValue3 As String, _
Optional InValue4 As String, Optional InValue5 As String, Optional InValue6 As String, _
Optional InValue7 As String, Optional InValue8 As String, Optional InValue9 As String) As Variant






Select Case inValue




     Case "1a", "2b", "3c"
          IslikeNumber = "company1"
     


     Case "4d", "5e", "6f", "7g"
          IslikeNumber = ""company2"
     
     
     Case "8h", "9i", "10j", "11k"
      IslikeNumber = "company3"




'OTHERS
    Case Else
        IslikeNumber = "Other"




End Select






End Function

Also, there are thousands of company numbers for each company name. In the above I've removed most for easier reading.



any help anyone can offer will be greatly appreciated.
Matei