Matching multiple criteria in VBA

VanMn

New Member
Joined
Jan 1, 2004
Messages
37
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))}
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi VanMn:

If you want to use the INDEX function formulation in VBA, one of the ways is to use ...
Code:
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!
 
Upvote 0
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
 
Upvote 0
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'
Code:
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 ...
Code:
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
Code:
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!
 
Upvote 0
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
 
Upvote 0
VanMn said:
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.
 
Upvote 0
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):
Code:
Cells(myRowNumberVariable, someCell.Column).Name = "criteria1"
anotherCell.Name = "criteria2"
This way I could use criteria1 and criteria2 in my Match function:
Rich (BB code):
Evaluate("MATCH(criteria1 & criteria2 & ... , A5:A9&B5:B9&..., 0)")
Thank you Yogi Anand! Eight years later, still helpful :)
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,149
Messages
6,106,251
Members
448,010
Latest member
wbarkwell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top