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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now