Solved

Wildcard for Vlookup command

Posted on 2013-11-08
5
516 Views
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?
DummyRec.xlsx
0
Comment
Question by:Justincut
  • 2
  • 2
5 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
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:
=FIND(545488.28,I539)

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.
0
 
LVL 80

Expert Comment

by:byundt
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:
=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

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.
0
 

Author Comment

by:Justincut
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)
0
 
LVL 20

Expert Comment

by:ltlbearand3
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:
=IF(ISERROR(FIND(4807512,H539)),"NotFound",H541)

Open in new window


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

-Bear
0
 
LVL 80

Accepted Solution

by:
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.
DummyRecQ28289149.xlsm
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

706 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

15 Experts available now in Live!

Get 1:1 Help Now