Matching multiple criteria in VBA

V

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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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
Click to expand...
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

L
  • Question Question
VBA Match function for two criteria / columns
Replies
9
Views
209
Domenic
D
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
464
BrettOlbrys1
BrettOlbrys1
L
  • Solved
Using CELL function result as part of range in an AVERAGE formula
Replies
3
Views
342
Fluff
Fluff
M
  • Question Question
A search formula (like index match) that can pull the most recent date when multiple matches may occur based on the criteria figure.
Replies
2
Views
74
MARZIOTULLIO
M
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
77
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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