Is it possible to get multiple results in vlookup

[PuffDuff]

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.

[Leanne]

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.

[Hollyn]

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.

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