Is there a way to create RDBMS-behavior in Excel for row column lookups?

Is it possible to lookup values in excel in the following method:

Table 1

ID    |     Code
-----------------
1     |      I
1     |      J
1     |      K
2     |      I
2     |      J
2     |      L

Table 2

ID    |    I      |    J    |    K    |    L    
----------------------------------------------
1     |   14.40   |  12.33  |  9.21   |  
2     |   13.99   |  11.28  |         |   32.33

The lookup would be to add the column values in table 2 next to the table 'code' in table 1. So table 1 would change to:

Table 1

ID    |  Code  |  Amount
-------------------------
1     |   I    |   14.40
1     |   J    |   12.33
1     |   K    |   9.21 
1     |   L    |   
2     |   I    |   13.99
2     |   J    |   11.28
2     |   K    |   
2     |   L    |   32.33

As a reminder, this is a project being run in Microsoft Excel开发者_运维百科 2003.

Update

I believe I can use a vlookup on the first column and given I know the placement of the code fields, I could go this route but the issue would be I cannot copy and paste this formula across an entire column because the order of which codes may appear can vary (and are not the same from ID to ID).

You can use Index and Match

=INDEX($C$4:$E$6,MATCH(H3,$B$4:$B$6,0),MATCH(I3,$C$3:$E$3,0))

Match finds the position of your ID and code in the Table 2 row and column headers. Index uses those to return the intersection of the row & column.

Assuming table 1 is in cells A1:B7 and table 2 is in A10:E12, you can put this formula in c2 and copy it down to c7. It's an array formula, so you need to press ctrl-shift-enter after you enter it.

=SUM(IF($A$11:$A$12=A2,IF($B$10:$E$10=B2,$B$11:$E$12,0)))