VLookup using two criterias - looking for a efficient way
 

Hi,
I've been stucked with this issue for a little while and have not been able to find an elegant, efficient way to go about it.
Please consider the attachment, the problem i have is the following:
in tab Analysis lookup, I have a set of unique codes (Column A) which relate to a Contract Item pair (Column B and C), to which i would like to assign a colour from source data.

my source data (second tab) has a colour assigned to any contract-item pair available in the system.

The only way i have been able to look this data up is by coupling the values in column B and C so as to create a unique key (new column, =B2&C2), doing the same in my source data, a doing a vlookup accordingly. Simple but inefficient, and possibly problematic in the long run.

I could imagine doing a Index/match function, but it would require reorganising my source data table (i'm not entirely sure how to do this actually.

Any smart way of doing this that you could propose?

VLookup using two criterias - looking for a efficient way
 

Hi,

Am Wed, 14 Nov 2012 11:26:00 +0000 schrieb EB.Zilch:

The only way i have been able to look this data up is by coupling the
values in column B and C so as to create a unique key (new column,
=B2&C2), doing the same in my source data, a doing a vlookup
accordingly. Simple but inefficient, and possibly problematic in the
long run.

try in Analysis lookup D2:
=INDEX('Source data'!$C$2:$C$100,MATCH(B2&C2,'Source data'!$A$2:$A$100&'Source data'!$B$2:$B$100,0))
and enter the array formula with CTRL+Shift+Enter


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

Hi EB.Zilch

As you rightly say INDEX & MATCH. This is an ARRAY formula, CTRL + SHIFT + ENTER. In the Analysis sheet, cell D2 and copy down.

=INDEX('Source data'!$C$2:$C$73,MATCH($B2&$C2,'Source data'!$A$2:$A$73&'Source data'!$B$2:$B$73,0)). If needed you can wrap in error trapping formula. Hopefully I have understood what you require.

That's exactly what i was after. to think that reading this forum, i was under the impression that CTRL + SHIFT + ENTER was just a keyboard shortcut... :D

many thanks, this will prove very helpful!