## Passing a VBA array to a formula

I have two rows of data looking like this:

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

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

But it fails whenever the ID I am searching for is not the first in column A. (I did try to sort column A with no luck).

So I have come up with this workaround: instead I would use this formula

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

where `positions`

is a VBA function that return an array of rows that match specified ID. The function `positions`

is coded so that it return a `Variant`

. But it seems that the VBA Array is not passing to the formula in excel. When I evaluate the formula, `positions(E2)`

is equal to 0. (I have checked in VBA, and my array is correctly populated).

So how do I make my formula correctly interpret the VBA array?

UPDATE: Here is my code:

```
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
```

UPDATE 2: Here is the desired output

Show source

## Answers ( 2 )

Put this array formula in F2:

Confirm with Ctrl-Shift-Enter instead of Enter. If done correctly then Excel will put

`{}`

around the formula.Then copy over and down sufficient to cover all the data.

EDIT #1

If you can sort the data then you can avoid the array formula and use this normal formula:

Drawing on Is it possible to fill an array with row numbers which match a certain criteria without looping?, you can do this with array and VBA as so:

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

returns a string of the matching B values`[e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|")`

puts the string to each arraycode