Multiple Vlookup Results

[qwertyuk]

Hi,

Just needed some help in obtaining multiple vlookup results on a sheet. Example file attached. I have got the first result working using formula

=IF(ROWS(B$2:B2)>COUNTIF(H$19:H$495,A2),"",INDEX(I$19:I$495,SMALL(IF(H$19:H$495=A2,ROW(I$19:I$495)-ROW(I$19)+1),ROWS(B$2:B2))))


Eg.

1 A
2 B
1 C
1 D
2 E

Now using the formula above i have got 1 working as its giving me the correct results but it becomes blank on 2. I know the reason why its visible on the attachment but all i want to do is drag the formula down and it works.

1 A
1 C
1 D
2
2

Anyhelp would be appreciated or a macro to help
Thanks

[qwertyuk]

Just found this excellent code which roughly does the job. the way this works is that lets say using the example above that i just have 2 lines

1
2

the results are posted like

1 A,C,D
2 B,E

I am not good at VB but can some one help me automatically do the following instead of putting this in one cell it knows that 1 has 3 entries so it adds 2 additional lines (1 already exitst and puts them seperatlly? e.g.

1 A
1 C
1 D
2 B
2 E


If not a formula is suffice as per original request

thanks

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

[qwertyuk]

Accidently Saved it twice

[DonkeyOte]

I'm not entirely sure I follow you but if I do... a simple route would be as follows (avoiding use of Arrays)

Adapt your index key in Column J such that:

J19:
=IF(COUNTA($H19:$I19)=2,SUM(COUNTIF($H$19:$H$495,"<"&$H19),COUNTIF($I$19:$I19,"<="&$I19)/1000),"")
copied down to J495

Then

count of records to be returned
A1: =COUNT($J$19:$J$495)

A2:
=IF(ROWS(A$2:A2)>$A$1,"",INDEX(H$19:H$495,MATCH(SMALL($J$19:$J$495,ROWS(A$2:A2)),$J$19:$J$495,0)))
copied across matrix A2:Bx where x is last row you wish to use

You could also use a Pivot Table...