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

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