Index and Match Help Needed

[carl]

My data table is set up like so:

Data BOX1 BOX3 BOX5
ABC 3 1 2
DEF 1 7
HIJ 2 1

I am trying to find a formula for the body of the table below that will give
the following results. I think I need to use the INDEX and Match functions
but not sure:

New Table BOX1 BOX2 BOX3 BOX4 BOX5
ABC 3 0 1 0 2
DEF 0 0 1 0 7
HIJ 2 0 1 0 0
KLM 0 0 0 0 0

Thank you in advance.

 

[bj]

if your original data is in sheet 1 starting at A1
and your next table is in Sheet 2 Starting at A1
try in sheet 2 B2

=Vlookup($A2,Sheet1!$A$1:$AA$100,Match(B$1,Sheet1!$A$1:$AA$1),False)

and copy through your area

 

[carl]

thanks. i tried the formula but it does not seem to work correctly. if there
is a value in Row B that is not in the original data table -
Sheet1!$A$1:$AA$1 - the formula returns a value from the previous column
(when it should return 0).

any thoughts ?

 

[bj]

duh
I left out several significant parts of it
the zero in the match funciton and the iserror portion

=if(iserror(Vlookup($A2,Sheet1!$A$1:$AA$100,Match(B$1,Sheet1!$A$1:$AA$1,0),False)),0,Vlookup($A2,Sheet1!$A$1:$AA$100,Match(B$1,Sheet1!$A$1:$AA$1,0),False))