Matching multiple criteria in VBA

[VanMn]

I would like to find a match using more than one criteria in vba. I have found good examples of formula solutions, but I haven't been able to find or figure out a simple vba solution yet.

With the example below I am trying to find the hours "Joe" worked on job "b". The simple answer is 3, but arriving at that value in vba eludes me. Any suggestions would be appreciated.

Employee Job Hrs Worked
Fred a 2
Joe a 4
Joe b 3
Sam b 2


One array formula example is
{=INDEX($C$2:$C$5,MATCH("Joe"&"b",$A$2:$A$5&$B$2:$B$5,0))}

 

[Yogi Anand]

Hi VanMn:

If you want to use the INDEX function formulation in VBA, one of the ways is to use ...

myValue = Evaluate("INDEX($C$2:$C$5,MATCH(""Joe""&""b"",$A$2:$A$5&$B$2:$B$5,0))")
    MsgBox "mvValue = " & myValue

I hope this helps!

 

[VanMn]

Wow Yogi, that’s great. I was actually trying to use the formula expression, but did not know about the evaluate method. I am grateful to learn something new at the same time getting an answer.

Thanks again
VanMn

 

[Yogi Anand]

Hi VanMn:

Good job in finding a solution to your problem -- now let us keep EXCELing!

 

[Yogi Anand]

Hi VanMn:

In response to your following question to me by PM ...

I hope you don't mind be sending a follow up question to what you helped me with yesterday.
I would like to have a variable instead of a hard code word in the evaluate method. This is what I have so far'

Sub Macro1() 
X = Range("a17").Value 'text value of Joe 
Y = Range("b17").Value 'text value of b 
myValue = Evaluate("INDEX($C$2:$C$5,MATCH(""X""&""Y"",$A$2:$A$5&$B$2:$B$5,0))") 
MsgBox "mvValue = " & myValue 
End Sub

When I run this I get a "Type Mismatch Error. I have tried many different combinations of quotes and ampersands, but have not been able to get it. I would be replacing the range values with a ComboBox value as I get it to work. If you have the time to reply, I would be greatfull.
VanMn

Since we are using the formula from the worksheet environment, here are couple of ways we can accomplish what you are trying to do ...

Sub y_1a()
    myValue = Evaluate("INDEX($C$2:$C$5,MATCH(A17 & b17,$A$2:$A$5&$B$2:$B$5,0))")
    MsgBox "mvValue = " & myValue
End Sub

or

by assigning named ranges X and Y for cells A17 and B17

Sub y_1b()
    ActiveWorkbook.Names.Add Name:="X", RefersTo:="=Sheet1!$a$17"
    ActiveWorkbook.Names.Add Name:="Y", RefersTo:="=Sheet1!$b$17"
    myValue = Evaluate("INDEX($C$2:$C$5,MATCH(X & Y,$A$2:$A$5&$B$2:$B$5,0))")
    MsgBox "mvValue = " & myValue
End Sub

I hope this helps!

 

[VanMn]

Yes this helps a great deal. The one combination I didn't try was to leave the quotes out completely. That was the key.
Thanks much

 

[Yogi Anand]

Yes this helps a great deal. The one combination I didn't try was to leave the quotes out completely. That was the key.
Thanks much

Hi VanMn:

The use of X and Y (without the quotes) in the formula will work only if the range names X and Y have been appropriately assigned beforehand as shown in my post.

 

[Lece]

I know this was long time ago, but I'm gonna post my solution anyway in case someone else will search for it as I just did.

I had a loop and on each iteration I had different MATCH criterias. This way I couldn't use a fixed cell as a MATCH parameter. The addresses of cells containing criterias were variables so I had to .Name them. The ActiveWorkbook.Names.Add Name wouldn't work because I coulnd't use variables in RefersTo parameter. And I couldn't afford myself dedicating a fixed cell to keep a temporary value of an address of another cell (I believe the code would look even worse than that sounded like). The solution was (thanks to Barrie Davidson):

Cells(myRowNumberVariable, someCell.Column).Name = "criteria1"
anotherCell.Name = "criteria2"

This way I could use criteria1 and criteria2 in my Match function:

Evaluate("MATCH(criteria1 & criteria2 & ... , A5:A9&B5:B9&..., 0)")

Thank you Yogi Anand! Eight years later, still helpful

 

[James Snyder]

...still useful 10 years later!

 

[hatstand]

Hi All,
I know this is a very old post, but it is close to answering my question. I cannot see why I'm getting an Error 13 on the Evaluate line. Could someone put me out of my misery and tell me where I'm going wrong.

Sub Macro1()

Dim x As String
Dim y As String
Dim R As String

x = Range("a16").Value
y = Range("b16").Value


R = Evaluate("INDEX($C$2:$C$20,MATCH(""X""&""Y"",$A$2:$A$20&$B$2:$B$20,0))")

MsgBox R
End Sub