Vlookup for multiple criteria, multiple worksheets
You can't reference entire columns as you did using the match function --
that is, Contacts!A:A='Contact roles'!D2 multiplied by another similar
formula would give a #NUM! error. Try this (note the range is limited to row
10000 -- feel free to increase it!
Ctrl/shift/enter:
=INDEX(Contacts!C:C,MATCH(D2&E2,Contacts!A$1:A$100 00&Contacts!B$1:B$10000,0))
Bob Umlas
Excel MVP
"jtoy" wrote:
Hi,
Since this is not a new topic, I tried altering some of the formulas already
posted to this forum for vlookups w/multiple criteria but with no luck.
I've got 2 tabs: Roles & Contacts
Resulting value will be entered on the Roles tab, in column C.
If value in D2 and E2 on the Roles tab matches values in columns A and B on
the Contacts tab, I would like to return value in column C from the Contact
tab.
I tried the following with no luck. Please help me find a formula!
=INDEX(Contacts!C:C,MATCH(1,(Contacts!A:A='Contact
roles'!D2)*(Contacts!B:B='Contact roles'!E2),0))
thanks!
|