Index match with multiple returns with vba

kwdennis

New Member
Joined
Jun 6, 2019
Messages
12
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 6, 2019
Messages
12
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
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
That is a totally different question & so needs a new thread. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,782
Messages
5,542,486
Members
410,556
Latest member
rebecamuresan
Top