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