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.
Formula for cell
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
Again, copy accross for as many cells as use wish