Results 1 to 3 of 3

Thread: Is it possible to get multiple results in vlookup

  1. #1
    Join Date
    Oct 2011
    Posts
    86

    Is it possible to get multiple results in vlookup

    I have a list of contract numbers (column A) and associated account numbers (column B). Some contract numbers are listed more than once because there may be multiple associated account numbers. My goal is to have one row/record for each contract number, with all the associated account numbers in a single cell, comma delimited. Can Excel do this.

  2. #2
    Join Date
    Jul 2011
    Posts
    638

    Re: Is it possible to get multiple results in vlookup

    You can do the same with the help of a macro. With a macro something like for each c in selection if c=yournumber then mylist=mylist&","&c next c msgbox mylist. I had not properly written the code but you can understand the same by editing it properly.

  3. #3
    Join Date
    Jul 2011
    Posts
    642

    Re: Is it possible to get multiple results in vlookup

    One more option: How about a UserDefinedFunction. It uses the almost the same syntax as the =vlookup() function. But it always uses "false" as the 4th argument--no matter what you type. Select a range (single column/single row) with enough cells to fill in your data (any cells not used will appear empty). Then type in your formula:=mvlookup2(a1,sheet2!$a$1:$c$999,3,false) (mvlookup2 = multiple Vlookup) (2 because this one is different from my original. You can change it (all spots) if you want to.

    Code:
    Option Explicit
    Function mvlookup2(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, _
    tableArray.Parent.UsedRange.EntireRow)
    On Error GoTo 0
    
    If initTable Is Nothing Then
    mvlookup2 = CVErr(xlErrRef)
    Exit Function
    End If
    
    initTableCols = initTable.Columns.Count
    
    i = 0
    Do
    myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)
    
    If IsError(myRowMatch) Then
    Exit Do
    Else
    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, _
    initTableCols)
    On Error GoTo 0
    If initTable Is Nothing Then
    Exit Do
    End If
    End If
    Loop
    
    If i = 0 Then
    mvlookup2 = CVErr(xlErrNA)
    Exit Function
    End If
    
    myStr = ""
    For i = LBound(myRes) To UBound(myRes)
    myStr = myStr & ", " & myRes(i)
    Next i
    
    mvlookup2 = Mid(myStr, 3)
    
    End Function

Similar Threads

  1. How to get multiple values of Vlookup in Single Cell
    By Henriksen in forum MS Office Support
    Replies: 4
    Last Post: 25-02-2012, 10:02 AM
  2. Getting multiple matches result on Vlookup sum
    By TheCarter in forum MS Office Support
    Replies: 2
    Last Post: 17-02-2012, 01:08 PM
  3. Excel - Multiple vlookup
    By mike_2011 in forum Windows Software
    Replies: 1
    Last Post: 28-07-2011, 03:40 AM
  4. Multiple Matches Return To VLOOKUP
    By Brunon in forum Windows Software
    Replies: 5
    Last Post: 26-11-2010, 10:32 PM
  5. Excel : vlookup multiple values
    By Laler in forum Windows Software
    Replies: 3
    Last Post: 25-06-2009, 10:27 AM

Posting Permissions

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