# Index match with multiple returns with vba

#### kwdennis

##### New Member
Cannot seem to get this to work once I start putting it into multiple return setup with in vba works fine in excel

VBA Code:
``````Public Sub TestMe()
'Range("G1").FormulaArray = "=INDEX(A2:A6,MATCH(F1,B2:B6,0))" ' This works
'Range("G2").FormulaArray = "=IF(ISERROR(INDEX(\$A\$2:\$A\$6,SMALL(IF(\$B\$2:\$B\$6=\$F\$1,ROW(\$A\$2:\$A\$6)),ROW(1:1)),1)),"",INDEX(\$A\$2:\$A\$6,SMALL(IF(\$B\$2:\$B\$6=\$F\$1,ROW(\$A\$2:\$A\$6)),ROW(1:1)),1))" ' This gives me a 1004 Error
End Sub``````

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
You need to double up the quotes inside the formula like
VBA Code:
``    Range("G2").FormulaArray = "=IF(ISERROR(INDEX(\$A\$2:\$A\$6,SMALL(IF(\$B\$2:\$B\$6=\$F\$1,ROW(\$A\$2:\$A\$6)),ROW(1:1)),1)),"""",INDEX(\$A\$2:\$A\$6,SMALL(IF(\$B\$2:\$B\$6=\$F\$1,ROW(\$A\$2:\$A\$6)),ROW(1:1)),1))" ' This gives me a 1004 Error``

#### kwdennis

##### New Member
Thanks,

The smallest differences between excel formula and vba formulas sometimes

Is there a more efficient way to handle this since it only returns the one value per row(1:1)

perhaps a case statement since F1 will be the value checked and it be a drop down list to check

or a case and countif and return all the matching values from single code line setup

VBA Code:
``````Public Sub TestMe()

Range("G2").FormulaArray= "=IF(ISERROR(INDEX(\$A\$1:\$A\$35,SMALL(IF(\$B\$1:\$B\$35=\$F\$1,ROW(\$B\$1:\$B\$35)),ROW(1:1)),1)),"""",INDEX(\$A\$1:\$A\$35,SMALL(IF(\$B\$1:\$B\$35=\$F\$1,ROW(\$B\$1:\$B\$35)),ROW(1:1)),1))"

Range("G3").FormulaArray=  "=IF(ISERROR(INDEX(\$A\$1:\$A\$35,SMALL(IF(\$B\$1:\$B\$35=\$F\$1,ROW(\$B\$1:\$B\$35)),ROW(2:2)),1)),"""",INDEX(\$A\$1:\$A\$35,SMALL(IF(\$B\$1:\$B\$35=\$F\$1,ROW(\$B\$1:\$B\$35)),ROW(2:2)),1))"

Range("G4").FormulaArray=  "=IF(ISERROR(INDEX(\$A\$1:\$A\$35,SMALL(IF(\$B\$1:\$B\$35=\$F\$1,ROW(\$B\$1:\$B\$35)),ROW(3:3)),1)),"""",INDEX(\$A\$1:\$A\$35,SMALL(IF(\$B\$1:\$B\$35=\$F\$1,ROW(\$B\$1:\$B\$35)),ROW(3:3)),1))"

End Sub``````

#### Fluff

##### MrExcel MVP, Moderator
That is a totally different question & so needs a new thread. Thanks.

Replies
0
Views
41
Replies
4
Views
330
Replies
3
Views
576
Replies
13
Views
126
Replies
6
Views
111