2D array lookups
 

I have a worksheet that tracks personal information, date
information and column flags on repeating rows, ie.
subject A001, 14-Apr-2004, S Flag, B Flag, W2 Flag etc...

The problem I have is, that the column flags are collected
on consecutive rows with the date as there unique
identifier...

S B W2 W3
A001 | 14-Apr-04 | X | | | |
A001 | 22-Apr-04 | | X | | |
A001 | 23-Apr-04 | | | X | X |

I would like to combine all the information from this
sheet into a second sheet to show the dates beside the
flags on the SAME row in order to manipulate the data...

S Date B Date W2 Date
A001 | X | 14-Apr-04 | X | 22-Apr-04 | X | 23-Apr-04 |


I tried to use VLOOKUP but it will not recognize the
subsequent rows as it checks the first row of the subject,
ie. A001, looks up the flags, then if it doesn't find
anymore matches it quits...therefore not looking at the
subsequent rows where the subject has further
information...

I tried to use concatenation to uniquely identify the row
record for the "lookup_value" of the VLOOKUP command
working on the sheet (I used the subject and column
heading in the concatenation...A001S), but I can't use the
same in the "table_array" portion of the function for
matching...


Can some one help...