The MATCH function with multiple criteria in VBA

[Lece]

Hi everyone!

I am trying to code the following Microsoft's example in VBA: How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel. On my worksheet (i.e. in Excel GUI) everything works fine. But I can't make it work in VBA.

For instance, I have a table with 3 columns: Name, Last Name and Profession.
I need to pick a record with Adam (A2) Smith (B2) who is an economist (C2).

Here's my worksheet formula. It's an array formula so after typing I Ctrl+Shift+Enter it.

{=MATCH(A2&B2&C2,A5:A9&B5:B9&C5:C9,0)}

In VBA, first of all, I define result as Variant (because I will keep getting the "Type mismatch" error since the Match function can return not only an integer, but also an error if nothing found -- this info might be useful in case someone will be googling the same problem):

Dim result As Variant

I've tried the following:

name = Range("A2").Value
nameR = Range("A5:A9")
lastname = Range("B2").Value
lastnameR = Range("B5:B9")
profession = Range("C2").Value
professionR = Range("C5:C9")
result = Application.WorksheetFunction.Match(name & "&" & lastname & "&" & profession, nameR & "&" & lastnameR & "&" & professionR , 0)

The code above returns "Type mismatch" error since, I guess, the "&" (ampersand) concatenates only string values which nameR, lastnameR etc. are not.

match_formula = "Match(" & name & "&" & lastname & "&" & profession & ", " & nameR.Address & "&" & lastnameR.Address & "&" & professionR.Address & ", 0)"
result = Evaluate(match_formula)

Still no success: it fills the target cell with a #VALUE! error.

Please help me. Thank you in advance.

 

[Lece]

Solved.

Thanks to Yogi Anand's idea of using named ranges.

Here's my working code:

Cells(myRowNumberVariable, someColumn.Column).Name = "criteria1"
match_formula = "MATCH([I]criteria1[/I] & criteria2 & ... , A5:A9&B5:B9, 0)"
result = Evaluate(match_formula)

 

[mikerickson]

The Evaluate path is correct, try

match_formula = "Match(" & name & lastname & profession & ", " & Range(nameR,professionR).Address(,,,True) & ", 0)"
result = Evaluate(match_formula)

 

[AlphaFrog]

Here's a slightly different Match formula for what it's worth.

{=MATCH(1,(A2=A5:A9)*(B2=B5:B9)*(C2=C5:C9),0)}

 

[Lece]

Thank you guys for your input!

 

[vidplaylist]

Hi,

Could you please help me with this code: i want to use index match function in loop with multi criteria,

Thanks,


Sub multicriteria()
Dim indexrange As Range
Dim Matchrange1 As Range
Dim Matchrange2 As Range

Set Matchrange1 = Sheets("Sheet1").Range("D1", Sheets("Sheet1").Range("D65536").End(xlUp))
Set indexrange = Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A65536").End(xlUp))
Set Matchrange2 = Sheets("Sheet1").Range("E1", Sheets("Sheet1").Range("E65536").End(xlUp))
Sheets("Sheet1").Range("B1", Sheets("Sheet1").Range("B65536").End(xlUp).Address).Name = "Criteria1"
Sheets("Sheet1").Range("C1", Sheets("Sheet1").Range("C65536").End(xlUp).Address).Name = "Criteria2"
MsgBox Application.Index(indexrange, _
Application.Match(Criteria1, Criteria2, Matchrange1, Matchrange2, 0)).Value

End Sub

Solved.

Thanks to Yogi Anand's idea of using named ranges.

Here's my working code:

Cells(myRowNumberVariable, someColumn.Column).Name = "criteria1"
match_formula = "MATCH([I]criteria1[/I] & criteria2 & ... , A5:A9&B5:B9, 0)"
result = Evaluate(match_formula)

 

[wingmarshall]

The Evaluate path is correct, try

match_formula = "Match(" & name & lastname & profession & ", " & Range(nameR,professionR).Address(,,,True) & ", 0)"
result = Evaluate(match_formula)

MsgBox WorksheetFunction.Index(Range("E2:E" & lastrow), Evaluate("WorksheetFunction.Match(" & xStr & Healthcheck & ", " & Range("C2:C" & lastrow) & Range("D2:D" & lastrow) & ", 0)"))


Why I am getting error can u help me on this