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:
Originally Posted by EB.Zilch
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?
|