Works great! thank you!

"Bob Umlas, Excel MVP" wrote:

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!