Hello Experts,

Just wondering if there's a way to do a lookup with multiple criteria in 2 different workbooks.

i.e. wb1 were values that I need to lookup.
wb2 were the values where I need to lookup at.

in excel formula simple term, vlookup(wb1, wb2, col-index, false)

That being said, I have named ranged in wb2, for easy reference.

Thus, ASSUMING that I have CustName at wb1 and wb2, with additional info at wb2 such as birth date and gender, how would i go about checking and returning the value (country) for me.

example:
wb1 - Dom Lian (custName)
- Male (Gender)
- Jan 1, 1990 (DOB)

wb2 - Dom Lian (custName)
- Male (Gender)
- Jan 1, 1990 (DOB)
- USA (Country)

- Dom Lian (custName)
- Male (Gender)
- Jan 1, 2012 (DOB)
- USA (Country)


I could do this in Excel with vlookup array formula...but do not know how to do this on VBA.

I tried to record the formulated macro...but it gives me a type mismatch.

currResult = Workbooks(wbName).Worksheets(wsName).Evaluate("vlookup(" & lkPartnerCode & "," & _
    "if(" & lkInvoicePeriodEarliest & ">= value(year(" & EffectiveDate & "text(month(" & EffectiveDate & "), ""00""))))" & _
    "*(" & lkInvoicePeriodLatest & "<= value(year(" & EndDate & "text(month(" & EndDate & "), ""00"")))," & PartnerData & ",""""),6,0)")