Results 1 to 8 of 8

Thread: Solved: Vlookup with Multiple Returns

  1. #1

    Solved: Vlookup with Multiple Returns


    I am performing a vlookup where the values I am looking up have more than match.

    Rather than just 1 value, I would like all the matches to be listed. Ideally, I would like all matches to appear in 1 cell with commas separating them, but perhaps that is asking to much.

    Otherwise, I would like the matches to be listed in columns moving towards the right of my formula cell.

    I have searched high and low for the solution to this problem. Can anyone help me?

    Thank you

  2. #2
    I have solved my problem. Hooray

    I hope this helps someone else.

    [VBA]Option Explicit
    Function mvlookup(lookupValue, tableArray As Range, colIndexNum As Long, _
    Optional NotUsed As Variant) As Variant

    Dim initTable As Range
    Dim myRowMatch As Variant
    Dim myRes() As Variant
    Dim myStr As String
    Dim initTableCols As Long
    Dim i As Long
    Dim ubound_myRes As Long

    Set initTable = Nothing
    On Error Resume Next
    Set initTable = Intersect(tableArray, _
    On Error GoTo 0

    If initTable Is Nothing Then
    mvlookup = CVErr(xlErrRef)
    Exit Function
    End If

    initTableCols = initTable.Columns.Count

    i = 0
    myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)

    If IsError(myRowMatch) Then
    Exit Do
    i = i + 1
    ReDim Preserve myRes(1 To i)
    myRes(i) _
    = initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
    If initTable.Rows.Count <= myRowMatch Then
    Exit Do
    End If
    On Error Resume Next
    Set initTable = initTable.Offset(myRowMatch, 0) _
    .Resize(initTable.Rows.Count - myRowMatch, _
    On Error GoTo 0
    If initTable Is Nothing Then
    Exit Do
    End If
    End If

    If i = 0 Then
    mvlookup = CVErr(xlErrNA)
    Exit Function
    End If

    myStr = ""
    For i = LBound(myRes) To UBound(myRes)
    myStr = myStr & ", " & myRes(i)
    Next i

    mvlookup = Mid(myStr, 3)

    End Function[/VBA]

  3. #3
    I got error #name when i applied this formula.
    A mighty flame followeth a tiny sparkle!!

  4. #4
    Works fine for me. It works the same way as vlookup, but you do not need the true or false at the end.

    So it's:


    Just dump it in a module, and away you go.

    Also, make sure to delete that Times New Roman business at the top that I forgot to remove. But I am sure you did already.

  5. #5
    Check this file with error #name?

    im using xl 98
    A mighty flame followeth a tiny sparkle!!

  6. #6


    Fixed! I just deleted the font info that should not have been included, and moved the macro to a module, rather than sitting in a workbook.

    And there you have it.

  7. #7
    Moderator VBAX Guru Ken Puls's Avatar
    Aug 2004
    Nanaimo, BC, Canada
    Hey Jacksonworld!

    Nice work! Just an FYI, I've removed those extra font tags from the post in case anyone else happens upon it.

    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!

  8. #8
    Hi, that works great!

    However im having a little problem getting it to work in code.
    When i try to put a range from another sheet in another file, initTable Is Nothing.

    What im trying to do is pass in a different range into the active workbook:

    Filename: NewFile.xls
    Sheetname: Sheet1
    Range: Entire Column A - Column F

    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open("NewFile.xls")
    Dim strResult As String

    strResult = mvlookup("A1", xlApp.Application.Worksheets("Sheet1").Range("A:F"), 2, False)

    Thanks for any help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts