VLookup using two criterias - looking for a efficient way

[EB.Zilch]

Hi,
I've been stucked with this issue for a little while and have not bee
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) whic
relate to a Contract Item pair (Column B and C), to which i would lik
to assign a colour from source data.

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

The only way i have been able to look this data up is by coupling th
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 vlooku
accordingly. Simple but inefficient, and possibly problematic in th
long run.

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

Any smart way of doing this that you could propose

+-------------------------------------------------------------------
|Filename: analysis-lookup.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=684
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[Kevin@Radstock]

Hi EB.Zilch

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

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

Hi,
I've been stucked with this issue for a little while and have not bee
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) whic
relate to a Contract Item pair (Column B and C), to which i would lik
to assign a colour from source data.

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

The only way i have been able to look this data up is by coupling th
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 vlooku
accordingly. Simple but inefficient, and possibly problematic in th
long run.

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

Any smart way of doing this that you could propose

+-------------------------------------------------------------------
|Filename: Copy of analysis-lookup.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=685
+-------------------------------------------------------------------

 

[EB.Zilch]

Hi EB.Zilch

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

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

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

many thanks, this will prove very helpful

+-------------------------------------------------------------------
+-------------------------------------------------------------------