How to get each value from one column to another column based on its corresponding value

advertisements

As you can see in the image that "Col B" has same number multiple times. For ex: "1" is four times, "2" is three times, and so on. However, all these numbers correspond to a specific number from "Col A". What I am trying to do is get the the column I have highlighted in orange and yellow. You can clearly see what I have done. What I need is a excel function that does it for me. This is just a sample. I have dataset with million data points, and I can't type all that.

Thanks!!


Formula for cell E2

=IFERROR(SMALL(IF($B:$B=$D2,ROW($B:$B)-1),COLUMN(A:A)),"")

Entered as an Array Formula (Enter with Ctrl-Shift-Enter rather than just Enter)

Copy accross for as many cells as you wish

Note: this formula is quite slow. A well designed UDF will be faster.

One way to solve this with a UDF is

Function MultiLookup(Val As Variant, rItems As Range, rLookup As Range, Index As Long) As Variant
    Dim vItems As Variant
    Dim i As Long, n As Long

    With rItems
        If IsEmpty(.Cells(.Count)) Then
            Set rItems = Range(.Cells(1, 1), .Cells(.Count).End(xlUp))
        End If
    End With

    vItems = rItems
    n = 0
    For i = 1 To UBound(vItems, 1)
        If vItems(i, 1) = Val Then
            n = n + 1
            If n = Index Then
                MultiLookup = rLookup.Cells(i, 1)
                Exit Function
            End If
        End If
    Next
    MultiLookup = vbNullString
End Function

Use like this, for cell E2

=MultiLookup($D2,$B:$B,$A:$A,COLUMN(A:A))

Again, copy accross for as many cells as use wish