Index and Match Help Needed

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.

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" wrote:

> 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.
>
>
>

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" wrote:

> 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" wrote:
>
> > 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.
> >
> >
> >

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))

"carl" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > >
> > >
> > >