Vlookup for multiple criteria, multiple worksheets
 

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!

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!

Vlookup for multiple criteria, multiple worksheets
 

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='Contac t
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" wrote in message
...
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!

Vlookup for multiple criteria, multiple worksheets
 

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!

Vlookup for multiple criteria, multiple worksheets
 

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

"T. Valko" wrote:

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='Contac t
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" wrote in message
...
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!