Vlookup (or another method) for searching

[bradjedis]

Greetings,

Here is the situation. I have two tabs. First Tab data listing in column A is explicit. The Target Tab of data, column A contains MORE data. I would like to lookup the the value in the first sheet for a match in the second tab as a partial or contains.

for instance

First tab col A
123h4512

Second tab col A
123h4512.abc
abc123h4512abc.abc


there could be preceding characters for the second tab information. or following...the value of the first tab col A could be buried in the text of the target search data

looking for a formula, or Macro if needed.

[HansV]

Like this in a cell in row 2 of the first tab, if you want to return the corresponding value from column C of the second tab:

=INDEX('Second Tab'!$C$2:$C$1000, MATCH("*" & A2 & "*", 'Second Tab'!$A$2:$A$1000, 0))

Modify the sheet name and the ranges as needed, then fill down.

Similar for other columns.

[bradjedis]

Hans, That worked great. Now I found that there is a distinct possibility of multiple hits to the queried item. Is it possible to return ALL the hits into the cell?

for instance: Item queried: 45623 First hit is 045623, then 0045623, 456230..

so the result in the cell is 045623, 0045623, 456230

I know I had this circumstance years ago, but I just cannot find the code.

If the =INDEX('Second Tab'!$C$2:$C$1000, MATCH("*" & A2 & "*", 'Second Tab'!$A$2:$A$1000, 0)) Can be augmented to include this thought, great, or if there is another solution that is great as well. I am not concerned about the hit on performance, as I will just this churn overnight...

Thanks,
Brad

[HansV]

Here is a variation on Steve Aprahamian (sdckapr)'s VLookupAll function:

Code: Select all

Function VLookupAll(vValue, rngAll As Range, iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
        If rCell.Value Like vValue Then
            VLookupAll = VLookupAll & sSep & rCell.Offset(0, iCol - 1).Value
        End If
    Next rCell
    If VLookupAll = "" Then
        VLookupAll = CVErr(xlErrNA)
    Else
        VLookupAll = Mid(VLookupAll, Len(sSep) + 1)
    End If
    Exit Function
ErrHandler:
    VLookupAll = CVErr(xlErrValue)
End Function

Use like this:

=VLookupAll("*" & A2 & "*", 'Second Tab'!$A$2:$C$1000, 3)

[bradjedis]

Very nice. Does Steve have a separate web site? Other fun things like this?

Thanks,
Brad

[HansV]

Er, no. Steve was a very active member of Woody's Lounge (later WindowsSecrets Lounge), Eileen's Lounge and other forums, but he isn't active anymore.

[bradjedis]

Hans,

Quick question, for the above code on the 15th of April, when it is proccessing the vlookup, is it looking for exact match?

example: in the target data I have 123ab123, and 123ab1234. I am needing to ensure it is exact match.


Thanks,
Brad

[HansV]

If the first argument is a string without wildcards, VLookupAll will search for cells with that exact string as value.
If the first argument contains ? and/or *, these characters will be treated as wildcards: ? can be any single character, and * can be any number of characters (including none).

So the formula

=VLookupAll("123ab123", 'Second Tab'!$A$2:$C$1000, 3)

will concatenate all cells in column C of Second Tab for which the corresponding cell in column A has value exactly equal to 123ab123.

But the formula

=VLookupAll("*ab*", 'Second Tab'!$A$2:$C$1000, 3)

will concatenate all cells in column C of Second Tab for which the corresponding cell in column A contains ab (with possibly extra text before and after it).

[bradjedis]

Thanks for the explanation, and that works....


BRad