Wildcard for Vlookup command

Posted on 2013-11-08
Last Modified: 2013-11-14
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?
Question by:Justincut
  • 2
  • 2
LVL 20

Expert Comment

ID: 39635245
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)

Open in new window

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:

Open in new window

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.
LVL 81

Expert Comment

ID: 39635258
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:

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
End If
VlookupWild = vResult
End Function

Open in new window

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.

Author Comment

ID: 39636754
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)
LVL 20

Expert Comment

ID: 39636970
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:

Open in new window

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

LVL 81

Accepted Solution

byundt earned 500 total points
ID: 39637007
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.

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question