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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

739 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