hi,

Am Mon, 11 Mar 2013 21:10:34 +0000 schrieb srm6:

Sheet 1
Name Location Code Task Answer should be:
{Formula Here} IL001 010 Bob
{Formula Here} IL001 009 Bill
{Formula Here} IL002 008 Diane
{Formula Here} IL002 007 Tina
{Formula Here} IL003 006 Bill
{Formula Here} IL004 005 Tina
{Formula Here} IL004 004 Bob
{Formula Here} IL005 003 Diane
{Formula Here} IL006 002 Tina
{Formula Here} IL007 001 Bob
{Formula Here} IL008 000 Diane

Sheet 2
Location Code Task Name
IL008 000 Diane
IL007 001 Bob
IL006 002 Tina
IL005 003 Diane
IL004 004 Bob
IL004 005 Tina
IL003 006 Bill
IL002 007 Tina
IL002 008 Diane
IL001 009 Bill
IL001 010 Bob

try:
=INDEX(Sheet2!$C$2:$C$200,MATCH(B2&C2,Sheet2!$A$2: $A$200&Sheet2!$B$2:$B$200,0))
and enter the array formula with CRTL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2