Transpose Data - Help Creating Dummy Variables or Transposing missing parameters as zero

Hello,

The best description of my problem is that I have a long list of data in rows where a sample (column A) has subset of the total variables (column B). I'd like to convert this into a matrix, where all 40 variables are listed as columns (i.e. transpose) and where all non-existent variables (i.e. missing rows) appear as zero values. This is made difficult b/c not every sample contains every variable and there are no dummy values as place holders. I've tried combining nested IF statements and an index/match function and have been successful to a point. However, a) I don't think this is the most elegant solution, so I'd be interested to hear other approaches and b) some of the outputted values are incongruent (i.e. numerical values don't match the correct "match") and whole columns are returning zero values when they shouldn't be. I think the latter problem may be some bug in the software, b/c I cannot understand how some columns display proper output, while others don't. It appears to be a problem with the LOOKUP function, though I'm not sure how b/c I can search the column of interest for the term and find it no problem.

Please find the spreadsheet attached. The formula in question is:

=IF(AND(LOOKUP($P2,$E$2:$E$1630)=$P2, LOOKUP(T$1,$J$2:$J$1630)=T$1),INDEX($L2:$L1630,MATCH(T$1,$J$2:$J$1630,0)),0)

Thanks

Thanks! You are definitely right that the problem is with the ordering of the data prior to using the function LOOKUP. I was able to (with the help of a friend) solve my problem by avoiding using LOOKUP and instead simply CONCATENATING the two variables I was lookingup and then directly searching for that combination using MATCH re: INDEX(range,MATCH(TXT, range)).

Thanks for the help,