# Index and Match Help Needed

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

2. ## RE: Index and Match Help Needed

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)

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

3. ## RE: Index and Match Help Needed

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

4. ## RE: Index and Match Help Needed

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1