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))}
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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!
 

VanMn

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi VanMn:

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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!
 

VanMn

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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.
 

Lece

New Member
Joined
Jan 14, 2012
Messages
15
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:

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
762
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

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
Top