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

 

[excelland]

Dear Yogi Anand,

Thank you for the amazing solution! However, I encountered a problem with the formula when the searching range is unknown and dynamic. The searching range is not always $A$2:$A$5&$B$2:$B$5, but it is A1 until the last non-blank row (which might change when my data is changed). How should I handle this?

Any help is very appreciated! Thank you very much.

 

[Nubster]

Hello all,

I have to match 2 values.
One is a string and the other a number out of 327000 records and only 1 is unique. (I am testing on 200 rows)

so far I have:

sub test ()
Application.ScreenUpdating = True

yourTemplate = "ADRESSEN.xlsm"
yourPath = "DRIVE:\"
lOpen = True
Workbooks.Open Filename:=yourPath + yourTemplate, Editable:=True

Workbooks("ADRESSEN.xlsm").Names.Add name:="X", RefersTo:="=Adressen!$e$2" 'x = "9999AZ"
Workbooks("ADRESSEN.xlsm").Names.Add name:="Y", RefersTo:="=Adressen!$d$2" 'y = "39" doesnt work either
Set xr = Workbooks("ADRESSEN.xlsm").Sheets("ADRESSEN").Range("l2:l200")
Set yr = Workbooks("ADRESSEN.xlsm").Sheets("ADRESSEN").Range("f2:f200")
Set ind = Workbooks("ADRESSEN.xlsm").Sheets("ADRESSEN").Range("e2:e200")

myValue = Evaluate("INDEX(ind,MATCH(X & Y, Xr & Yr,0))")
MsgBox "mvValue = " & myValue

Application.ScreenUpdating = True

end sub

I keep getting error 13 type mismatch

How can I get it to work?