VBA - INDEX MATCH MATCH

[Octofez2]

Hey All,

I'm curious to know if I have something incorrect with the below or should go about it differently. I've tested the ranges by splitting up the formula to ensure the ranges are setup correctly, they are. Currently, I'm just trying to get the formula to work and then utilize some variables. Thanks for having a look.

         Dim modlv As Variant
         
           
            modlv = Application.Index(Sheets("Poll").Range("I4:I7500"), _
                    Application.Match(Sheets("Info").Range("E15"), Sheets("Poll").Range("A4:A7500"), 0), _
                    Application.Match(Sheets("Info").Range("C22"), Sheets("Poll").Range("G4:G7500"), 0))
                   
        If IsError(modlv) = False Then
            Range("D22") = modlv
            Else
            Range("D22") = ""
        End If

 

[Kenneth Hobson]

Say in words or attach an example file with data and standard formulas that does what you want.

 

[Octofez2]

Below is a formula in a nearby cell, if it helps. Posting a sample would be very difficult.

What I'm trying to accomplish in VBA is pulling data based off of 2 different criteria. I had difficulty following along with what I could find in forums mentioning about creating one match as an array and using that for the second match. Leaving formulas in cells or using VBA to insert formulas gets messy when users accidentally select a cell with an array in it. Thank you for checking out the post.

{=IFERROR(INDEX(Poll!AE:AE,MATCH(Info!$E$15&Info!$C17,Poll!$A:$A&Poll!$G:$G,0)),"")}

 

[Octofez2]

I think I got it. It appears to be working. Used And between the 2 matches in the formula. I am still open for ideas if anyone has any. Thank you.

            modlv = Application.Index(Sheets("Poll").Range("I4:I7500"), _
                    Application.Match(Sheets("Info").Range("E15"), Sheets("Poll").Range("A4:A7500")) And _
                    Application.Match(Sheets("Info").Range("C22"), Sheets("Poll").Range("G4:G7500")), 0)

 

[Nebu]

Hi:

May be this code

With Application.WorksheetFunction
modlv = IfError(.Index(Sheets("Poll").Range("AE:AE"), .Match(1, .MMult((Sheets("Info").Range("$E$15") = Sheets("Poll").Range("$A:$A")) * (Sheets("Info").Range("$C17") = Sheets("Poll").Range("$G:$G")), 1), 0)), "")
End With

Note: The excel formula will be :

=IFERROR(INDEX(Poll!AE:AE,MATCH(1,MMULT((Info!$E$15=Poll!$A:$A)*(Info!$C17=Poll!$G:$G),1),0)),"")

Thanks

 

[Octofez2]

Thank you Nebu. I could not get that formula to work due to the Mmult Function and IfError function. Excel kept throwing an error about a Sub or Function not defined. From my understanding the WorksheetFunction is not capable of dealing with an IfError statement. The solution I posted is not accurate so I'll keep messing around.

 

[Nebu]

Hi:

I had missed one dot in the code before MMULT, try including the dot the code should should work.

Thanks

 

[Octofez2]

Thanks Nebu and Ken for your assistance. I could not get the formula to work due to a mismatch error that I could not figure out. I ended up going with an Evaluate option which works fantastic. Here is a sample of the code I used for reference.

    Dim sudlv As Variant
    Dim sh As Worksheet
   
    Set sh = ("Info_Poll")
   
    sudlv = sh.Evaluate("=INDEX(Poll!O2:O7400,MATCH(Info!E15&Info!C22,Poll!A2:A7400&Poll!G2:G7400,0))")
   
       If IsError(sudlv) = False Then
            Range("D22") = sudlv
            Else
            Range("D22") = ""
        End If

 

[Nebu]

Hi:

You have to use evaluate since the formula you are using is an array formula. The formula I have given is an alternative even though the formula with MMULT is an array formula it is not executed using Control+Shift+Enter, since MMULT as a native function is an array.

Thanks