# Wildcard for Vlookup command

Hi Guys, is there a wildcard for the Vlookup command? If you look at my file, my Vlookup formula is looking for a Trade ID in the "Trade ID" column, which is usually a 8 digit number. However, 5% of the column carries multiple Trade Id number in the main cell so the Vlookup bring back #NA. Eg. if I look for the Trade id "545488.28" and the cell it is in has
"60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28", it will bring back NA#. See my file for the example in row 539. Is there a Formula which can do a Vlookup- IN string for example?
DummyRec.xlsx
###### Who is Participating?

x

Commented:
A regular VLOOKUP can do as you request if column H of the lookup table contains "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28" and you are looking for "112930.28"
=IFERROR(VLOOKUP(112930.28,H:J,3,FALSE),IFERROR(VLOOKUP("*" & "112930.28" & "*",H:J,3,FALSE),""))
First, the VLOOKUP is checking for 112930.28 as a number by itself in column H. Next, it checks for "112930.28" as text within cells in column H. If either value is found, the formula returns a value from column J on the same row. If not, the formula returns an empty string (looks like a blank).

The VlookupWild user-defined function can also do what you request if the lookup value is  "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28" and you want to search column H for any of those values.
=VlookupWild( "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28",H:J,3,FALSE)

I've attached a workbook showing the results of VlookupWild for your original problem in column BC. If you compare to your original formula, you'll see that it is pulling back a value in cell BC729 where your original formula failed.
DummyRecQ28289149.xlsm
0

Commented:
I guess I need better clarification for what you ultimately want to accomplish.  You can use a wildcard in the Vlookup function.  i.e. something like this:
``````=VLOOKUP("*" & A10 &"*",A2:A5,1,FALSE)
``````

However, wildcards only work on text arrays.  Also, the look up array (in your instance the Murex table) must be sorted.

The data on the spreadsheet does not match your questions.  You ask about looking up 545488.28 in the list of "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28."  However, what your sheet is trying to do is look up the value of "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28" in the list of Murex.  Because that list of Murex is all individual values, it will never find a match.

What are you really needing.  Are you wanting to see if 545488.28 in in the list of "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28".  If so then a simple find command could work.  For example:
``````=FIND(545488.28,I539)
``````

Or maybe you want to see if any value in the string "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28" is in the murex list?  This would most likely need to be done with VBA code.

Please provide some more details and we can see what can be done.
0

Commented:
I wrote a user defined function that would split the first parameter in a VLOOKUP into multiple substrings by using the comma as a separator character. I then used Trim to get rid of leading and trailing spaces and used the worksheet VLOOKUP function. If it returned an error value, I then repeated the test with Val(substring). If an error value was returned, then repeat the test with the next substring.

In so doing, I could rewrite your formula like this:
=IFERROR(VlookupWild(I532,GBO,1,FALSE),VlookupWild(H532,Murex,1,FALSE))

Suppose cell I532 contains "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28".  The VlookupWild function would search GBO for 60500.28. If not successful, it would search for 214875.28. If not successful, then search for 112930.28 and so on. VlookupWild returns the first of these values that it finds in named range GBO. If none of these values were found, then VlookupWild returns the #N/A error value.

The code for function VlookupWild goes in a regular module sheet (just like a macro) and looks like this:
``````Function VlookupWild(LookupVal As Variant, lookupTable As Range, iColumn As Integer, bSorted As Boolean) As Variant
Dim v As Variant, vResult As Variant
vResult = Application.VLookup("abcde", lookupTable, iColumn, bSorted)   'Put an error value in vResult
If LookupVal <> "" Then
For Each v In Split(LookupVal, ",")
v = Application.Trim(v)
vResult = Application.VLookup(v, lookupTable, iColumn, bSorted)
If Not IsError(vResult) Then Exit For
v = Val(v)
If v <> 0 Then vResult = Application.VLookup(Val(v), lookupTable, iColumn, bSorted)
If Not IsError(vResult) Then Exit For
Next
End If
VlookupWild = vResult
End Function
``````

It is worth noting that the above formula and user-defined function return an error value on row 539. This is because ID number 545488.28 (last entry in I539) is found in named range Errors rather than GBO.

Note also that the function VlookupWild is found in the function wizard under "User defined functions" at the bottom of the category list.
0

Author Commented:
Does the =Find function work like a Vlookup? What if I am looking for the value "112930.28"  in a cell "60500.28, 214875.28, 112930.28, 59295.28, 323976.28, 443444.28, 545488.28 and then I want to see what value is 2 cells to the right of that cell? eg, =find (112930.28,2,H:J,False)
0

Commented:
I am still not completely sure what you want to accomplish, but you could use the Find function to check if a value is in a a cell and move over a couple columns.  Find returns an error if the value is not in the search text.  Therefore you could do something like this:
``````=IF(ISERROR(FIND(4807512,H539)),"NotFound",H541)
``````

This would return the value in cell H541 if the number 4807512 was found in cell H539.

-Bear
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.