Index Match multiple criteria

[tiredofit]

Can someone tell me if this works in VBA?

https://trumpexcel.com/index-match/#Example-4-Lookup-Value-From-Multiple-ColumnCriteria

specifically:

=INDEX($C$2:$C$11,MATCH($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))

I've tried it like this:

Dim Output

For Cols = 1 To 10

For Rows = 1 To 20

Output = Application.WorksheetFunction.Index(OutputRng, _
                    Application.WorksheetFunction.Match(Sheet1.Cells(Rows, 1).Value & "|" & _
                                                        Sheet1.Cells(Rows, 2).Value & "|" & _
                                                        Sheet1.Cells(Rows, Cols).Value & "|" & _
                                                        Sheet1.Cells(2, Cols).Value, _
                                                        Crit1Rng & "|" & _
                                                        Crit2Rng & "|" & _
                                                        Crit3Rng & "|" & _
                                                        Crit4Rng, _
                                                        0))

Next Rows

Next Cols

but I get an error:

Run-time '13':

Type mismatch

Thanks

 

[GlennUK]

Show your definitions of OutputRng and all the criteria ranges.

 

[tiredofit]

Show your definitions of OutputRng and all the criteria ranges.

    Dim Crit1Rng As Range
    Set Crit1Rng = Sheet2.Range("A2:A10")
    
    
    Dim Crit2Rng As Range
    Set Crit2Rng = Sheet2.Range("B2:B10")

    Dim Crit3Rng As Range
    Set Crit3Rng = Sheet2.Range("C2:C10")

    Dim Crit4Rng As Range
    Set Crit4Rng = Sheet2.Range("D2:D10")


    Dim OutputRng As Range
    Set OutputRng = Sheet2.Range("E2:E10")

 

[GlennUK]

I'm realising that you are doing an array concatenate on ranges - and I don't think you can do that in VBA the way you want. Sorry. Time for a redesign, I'd say. Like maybe doing something like this:

    Dim Output
    Dim search_crit
    
    Dim Crit1Rng As Range
    Set Crit1Rng = Sheet2.Range("A2:A10")
    
    search_crit = Crit1Rng.Value
    
    Dim Crit2Rng As Range
    Set Crit2Rng = Sheet2.Range("B2:B10")
    
    Dim Crit3Rng As Range
    Set Crit3Rng = Sheet2.Range("C2:C10")
    
    Dim Crit4Rng As Range
    Set Crit4Rng = Sheet2.Range("D2:D10")

    For irow = 1 To 9
        search_crit(irow, 1) = search_crit(irow, 1) & "|" & Crit2Rng(irow) & "|" & Crit3Rng(irow) & "|" & Crit4Rng(irow)
    Next

to set up your criteria, and then use search_crit in the Match function, if you see what I mean?

 

[GlennUK]

By the way, I used

Option Base 1

on that code.

 

[tiredofit]

By the way, I used

Option Base 1

on that code.

Thanks, I'll try that.