Vlookup for multiple criteria, multiple worksheets

[jtoy]

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!

 

[Bob Umlas, Excel MVP]

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$10000&Contacts!B$1:B$10000,0))

Bob Umlas
Excel MVP

 

[T. Valko]

What version of Excel are you using? If you're not using Excel 2007 then in
your formula you can't use entire columns as range references:

=INDEX(Contacts!C:C,MATCH(1,(Contacts!A:A='Contact
roles'!D2)*(Contacts!B:B='Contact roles'!E2),0))

Also, I'm assuming you know that the formula is an array and needs to
entered with the key combination of CTRL,SHIFT,ENTER.

Biff

 

[jtoy]

Works great! thank you!

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$10000&Contacts!B$1:B$10000,0))

Bob Umlas
Excel MVP

 

[jtoy]

ah...thanks for the advice. I am using 2003. The formula provided above
worked great!