Index match with multiple returns with vba

[kwdennis]

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

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

 

[Fluff]

You need to double up the quotes inside the formula like

    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]

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

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]

That is a totally different question & so needs a new thread. Thanks.