Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. Quote:
Last edited by Kevin@Radstock : November 14th 12 at 06:18 PM |
#4
|
|||
|
|||
Quote:
many thanks, this will prove very helpful! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup for 2 criterias | Excel Worksheet Functions | |||
VLOOKUP WITH 2 CRITERIAS... | Excel Worksheet Functions | |||
IF and VLOOKUP - how efficient? | Excel Worksheet Functions | |||
VLOOKUP with 2 Criterias | Excel Worksheet Functions | |||
Q: Is this vlookup statement efficient? | Excel Programming |