Conditional LookUp with Transpose

Question

I have the following table in Excel 2010:

Code    Variable      2003          2004         2005
923587  WC05301       0.300926921   0.665902164  0.916134943
923587  WC05001       0.715744225   0.453420519  0.312655924
923587  WC08376       0.247108211   0.374099217  0.378376177
923587  WC08001       0.947697297   0.686620132  0.41852137
923587  WC09204       0.78860597    0.246313221  0.556252026
923587  PTBV          0.400760313   0.637586519  0.723963115
923587  DPS           0.840353147   0.782413662  0.823206141
923587  WC02201       0.515966677   0.242782576  0.289028551
923587  WC03351       0.107782133   0.873951446  0.76152958
923587  WC02001       0.599825005   0.330610221  0.254449218
923587  WC02101       0.787267498   0.174806764  0.319124298
923587  WC02051       0.841697111   0.067667619  0.261424441

I want to convert this table to the following format:

Code    Year WC05301 WC05001 WC08376 WC08001 WC09204 PTBV DPS WC02201 WC03351 WC02001 WC02101 WC02051                                       
923587  2003                                                
923587  2004                                                
923587  2005                                                
923587  2006                                                
923587  2007                                                
923587  2008                                                
923587  2009                                                
923587  2010                                                
923587  2011                                                
923587  2012                                                
923587  2013                                                
923587  2014    

I want the lookup the first empty cell in the intersection of (923587,WC05301,2003). This would yield a formula in the form of:

=INDEX(table,MATCH(Code&Year,A2:A&C1:H1,0),MATCH(Variable,B2:B))

However this doesn't return the proper value. I know it has something to do with conditioning on A2:A&C1:H1 but how to work around/adjust for this?

Answer 1

You were almost there. This works:

=INDEX($C$2:$E$13, MATCH($A17&CHAR(1)&C$16,$A$2:$A$13&CHAR(1)&$B$2:$B$13,0), MATCH($B17,$C$1:$E$1,0))

entered as an array formula i.e. pressing Ctrl-Shift-Enter. This formula can then be copied down and to the right.

Note how I use CHAR(1) as a delimiter when joining Code & Variable. This ensures that e.g. Code=123WC + Variable=456 and Code=123 + Variable=WC456 do not get mixed up. As delimiter you can use any character which you are sure will never appear in Code or Variable.

Answer 2

The problem is that one lookup range is vertical and the other is horizontal. I would suggest using SUMPRODUCT and put all the conditions one after the other:

=SUMPRODUCT($C$2:$E$13*($A$2:$A$13=$A16)*($C$1:$E$1=$B16)*($B$2:$B$13=C$15))

$C$2:$E$13 is the range.

($A$2:$A$13=$A16) is the first condition to match the codes.

($C$1:$E$1=$B16) is the second condition to match the years.

($B$2:$B$13=C$15) is the last condition to match the variables.

0 will be the result where there are no matches.

---

SUMPRODUCT is basically taking the whole range and checking if all three conditions are satisfied. Now though, if more than 1 number are satisfied, you will get the sum of all the matches as result instead of the first match if INDEX worked.