Pass VBA array to formula

Question

I have two rows of data that look like this:

I am trying to return all position numbers for each ID in the array. I tried

={IF(A2:A562=E2,B2:B562)}

But it fails whenever the identifier I'm looking for is not the first in column A. (I tried to sort column A without any luck).

So, I came up with this workaround: instead, I would use this formula

={INDEX(B2:B562,positions(E2))}

where positionsis a VBA function that returns an array of strings matching the specified ID. The function positionsis encoded so that it returns Variant. But it seems that the VBA array does not go over to the formula in excel. When I calculate the formula, it positions(E2)is 0. (I checked in VBA and my array is correctly filled).

So, how do I get the formula to correctly interpret the VBA array?

UPDATE: :

Function positions(idrange As Range) As Variant
Dim V As Variant
Dim l, nb As Integer
Dim id As Double

nb = 4
ReDim V(nb) As Variant

id = idrange.Value
Set cible = Sheet2.Range("B1")
For l = 1 To nb
    Set cible = Sheet2.Columns(2).Find(What:=id, After:=cible, _
                LookIn:=xlValues)

    V(l) = cible.Row - 1   
Next l
positions = Application.Transpose(V)

End Function

2:

Answer 1

F2:

=IFERROR(INDEX($B$2:$B$562,MATCH(1,($A$2:$A$562=$E2)*(COUNTIF($E$2:E2,$B$2:$B$562)=0),0)),"")

Ctrl-Shift-Enter Enter. , Excel {} .

.

---

№ 1

, :

=IF(COLUMN(A:A) <= COUNTIF($A:$A,$E2),INDEX($B:$B,MATCH($E2,$A:$A,0)+COLUMN(A:A)-1),"")

Answer 2

, ?, VBA :

• Filter(Application.Transpose(Application.Evaluate("=IF(A2:A100=OFFSET(E2," & lngCnt - 1 & ",0), (B2:B100),""x"")")), "x", False) B

"1", "4", "7", "10" A2

2. [e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|")

Sub GetEm()

Dim lngCnt As Long
'range of your codes from E2 down
y = [E2:E4]

For lngCnt = 1 To UBound(y)
     x = Filter(Application.Transpose(Application.Evaluate("=IF(A2:A100=OFFSET(E2," & lngCnt - 1 & ",0), (B2:B100),""x"")")), "x", False)
    [e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|")
Next
End Sub