excel vba - multiple criteria index match

Question

I'm trying to do a multiple criteria index match function in vba but I can't seem to get the results. The code I used is the following:

  wsDest.Range(wsDest.Cells(i, X), wsDest.Cells(i, X)) = _
  Application.WorksheetFunction.Index(wsSour.Range("C3:C8763"), _
    Application.WorksheetFunction.Match(wsDest.Cells(i, 1) & "&" & wsDest.Cells(i, 2), _
      wsSour.Range("A3:A8763") & "&" & wsSour.Range("B3:B8763"), 0), 0)

For the match portion I was trying to use excel's method of

=MATCH(criteria1 & criteria2,range1 & range2,0)

'&' is concatinate strings operator, i.e. "a" & "b" becomes "ab". You can not use '&' for put two ranges together. Do you try to fine a index on which criteria1 matches in range1 at that index and criteria2 matches in range2 at that index by the formula "MATCH(criteria1 & criteria2,range1 & range2,0)"? That will not work, because 'Match' does not have such function. — Fumu 7
– Fumu 7, Commented Jan 6, 2015 at 2:06
@Fumu7 - you can use MATCH() to do this on a worksheet. See for example - excel-easy.com/examples/two-column-lookup.html — Tim Williams
– Tim Williams, Commented Jan 6, 2015 at 2:09

Tim Williams · Accepted Answer · 2015-01-06 19:00:51Z

4

You can use the WorkSheet.Evaluate method to do this.

Here's a simple example:

enter image description here

Sub Tester()

    Dim v, sht, a1, a2

    Set sht = ActiveSheet

    a1 = sht.Cells(7, 1).Address(False, False)
    a2 = sht.Cells(7, 2).Address(False, False)

    v = sht.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A5&B2:B5,0)")

    sht.Range("B9") = v

End Sub

EDIT: here's a more robust example taking into account the different sheets

Sub Tester2()

    Dim v, shtDest, shtSrc, a1, a2, i

    Set shtDest = ThisWorkbook.Sheets("Dest")
    Set shtSrc = ThisWorkbook.Sheets("Source")

    i = 1

    a1 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 1).Address(False, False)
    a2 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 2).Address(False, False)

    Debug.Print a1, a2

    v = shtSrc.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A9&B2:B9,0)")

    If Not IsError(v) Then
        shtDest.Cells(i, 3).Value = shtSrc.Range("C2:C9").Cells(v).Value
    End If

End Sub

edited Jan 6, 2015 at 19:00

answered Jan 6, 2015 at 2:08

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

L42 Over a year ago

Yes, Evaluate always treat formula as array formula. +1

sandrosil Over a year ago

@Tim Williams Thanks! I'll give this a try! But what if I the lookup array not to be a string? Can I do "," & (range1) & "&" & (range2) &",0)")?

Collectives™ on Stack Overflow

excel vba - multiple criteria index match

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related