# Matching multiple criteria in VBA

#### VanMn

##### New Member
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!

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

Hi VanMn:

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

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()
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!

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

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.

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:
...still useful 10 years later!

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

Replies
5
Views
129
Replies
1
Views
120
Replies
10
Views
386
Replies
11
Views
316
Replies
3
Views
95

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.

### Which adblocker are you using?

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

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