index match offset?

[denise]

Hello,

This is a wonderful discussion group, always so helpful. I'm hoping someone
will be able to help me with a problem I'm having trying to set up a new
workbook to pull selected results from an existing one. I am trying to figure
out the formula/function for cells B23 on workbook2:

EXISTING WORKBOOK1:
(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is
dynamic range 'mtd')
A B C D
Joe P1 7
P2 1
P3 2
Total 10
Jill P1 3
P2 4
P3 1
Total 8

NEW WORKBOOK2
A B C D
P1 P2 P3
Joe 7 1 2
Jill 3 4 1

I entered the following as an array formula in cell B2 and it works fine to
return the proper result of 7:
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
This does not work to return the results for P2 or P3, I am assuming because
I need some sort of an offset for the different rows in workbook1.

I am hoping someone can point me in the right direction here. First I want
to locate the proper name in workbook 1 and then the proper product under
that name and finally pull the mtd col D result for that product and name.

Thanks in advance!
Denise

 

[LanceB]

Joe P1 7
P2 1
P3 2


S/b

Joe P1 7
Joe P2 1
Joe P3 2

 

[Biff]

Hi!

(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is
dynamic range 'mtd')

Are you sure all of these ranges are evaluating to the same size?

It's pretty simple using just normal references:

=INDEX([Workbook1.xls]Sheet1!$D$1:$D$8,MATCH($A2,[Workbook1.xls]Sheet1!$A$1:$A$8,0)+COLUMNS($A:A)-1)

Copy across then down.

If you don't know how much data will be in the range just use an arbitrary
guess that you will never exceed like D11000.

Biff

 

[denise]

Lance, thanks for your suggestion. I see how this could solve the issue.
Unfortunately, workbook1 is produced by a different department and in
reality, with over 2 dozen "products", repeating the name before each product
would not make for an easily read or attractive spreadsheet report.

I was wondering if incorporating Offset into the formula would solve the
problem but I have been unable to get it to work.

Thanks,
Denise

 

[LanceB]

Understood

The different department issue makes it tough. You can use conditional
formatting to change the font to the background color on repeat entries to
attain the same look as you currently describe while still maintaining row
information for a match

 

[Biff]

I was wondering if incorporating Offset into the formula would solve the

problem but I have been unable to get it to work.

Offset won't work in a closed file. As long as the linked file is open it
will work but as soon as you close the linked file and the active file
calculates you'll get errors.

Biff

 

[denise]

Thanks Lance and Biff, both of these methods work!

Biff, I didn't get a chance to reply to you earlier but to answer your
question, no, the dynamic ranges were not the same size. Should they always
be? I did take your suggestion to just expand the cell references beyond what
I would ever need.

If you have a moment, could you explain how the COLUMNS ($A:A)-1 part works?

Thanks,
Denise

Hi!

(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is
dynamic range 'mtd')

Are you sure all of these ranges are evaluating to the same size?

It's pretty simple using just normal references:

=INDEX([Workbook1.xls]Sheet1!$D$1:$D$8,MATCH($A2,[Workbook1.xls]Sheet1!$A$1:$A$8,0)+COLUMNS($A:A)-1)

Copy across then down.

If you don't know how much data will be in the range just use an arbitrary
guess that you will never exceed like D11000.

Biff

Hello,

This is a wonderful discussion group, always so helpful. I'm hoping
someone
will be able to help me with a problem I'm having trying to set up a new
workbook to pull selected results from an existing one. I am trying to
figure
out the formula/function for cells B23 on workbook2:

EXISTING WORKBOOK1:
(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D is
dynamic range 'mtd')
A B C D
Joe P1 7
P2 1
P3 2
Total 10
Jill P1 3
P2 4
P3 1
Total 8

NEW WORKBOOK2
A B C D
P1 P2 P3
Joe 7 1 2
Jill 3 4 1

I entered the following as an array formula in cell B2 and it works fine
to
return the proper result of 7:
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
This does not work to return the results for P2 or P3, I am assuming
because
I need some sort of an offset for the different rows in workbook1.

I am hoping someone can point me in the right direction here. First I want
to locate the proper name in workbook 1 and then the proper product under
that name and finally pull the mtd col D result for that product and name.

Thanks in advance!
Denise

 

[LanceB]

How about
in B2
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
In c2
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0)+1)
In d2
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0)+2)

 

[Biff]

to answer your question, no, the dynamic ranges were not the same size.

Should they always be?

For use in this type of formula where you're referencing 2 different ranges,
yes.

could you explain how the COLUMNS ($A:A)-1 part works?

It increments the Match location as the formula is copied across a row.

Based on your sample data:

=INDEX(D18,MATCH("Joe",A1:A8,0))

Evaluates to:

=INDEX(D18,1))

And returns the value in D1.

Since there is only one instance of the look_up value, Joe, we need a means
to find all the values associated with Joe and since those values are in a
contiguous range we can simply increment the Match value. As you copy across
the COLUMNS function will do that incrementing for us:

=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:A)-1) = D1
=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:B)-1) = D2
=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:C)-1) = D3

And those evaluate to:

=INDEX(D18,1+0) = D1
=INDEX(D18,1+1) = D2
=INDEX(D18,1+2) = D3

Biff

Thanks Lance and Biff, both of these methods work!

Biff, I didn't get a chance to reply to you earlier but to answer your
question, no, the dynamic ranges were not the same size. Should they
always
be? I did take your suggestion to just expand the cell references beyond
what
I would ever need.

If you have a moment, could you explain how the COLUMNS ($A:A)-1 part
works?

Thanks,
Denise

Hi!

(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D
is
dynamic range 'mtd')

Are you sure all of these ranges are evaluating to the same size?

It's pretty simple using just normal references:

=INDEX([Workbook1.xls]Sheet1!$D$1:$D$8,MATCH($A2,[Workbook1.xls]Sheet1!$A$1:$A$8,0)+COLUMNS($A:A)-1)

Copy across then down.

If you don't know how much data will be in the range just use an
arbitrary
guess that you will never exceed like D11000.

Biff

Hello,

This is a wonderful discussion group, always so helpful. I'm hoping
someone
will be able to help me with a problem I'm having trying to set up a
new
workbook to pull selected results from an existing one. I am trying to
figure
out the formula/function for cells B23 on workbook2:

EXISTING WORKBOOK1:
(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D
is
dynamic range 'mtd')
A B C D
Joe P1 7
P2 1
P3 2
Total 10
Jill P1 3
P2 4
P3 1
Total 8

NEW WORKBOOK2
A B C D
P1 P2 P3
Joe 7 1 2
Jill 3 4 1

I entered the following as an array formula in cell B2 and it works
fine
to
return the proper result of 7:
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
This does not work to return the results for P2 or P3, I am assuming
because
I need some sort of an offset for the different rows in workbook1.

I am hoping someone can point me in the right direction here. First I
want
to locate the proper name in workbook 1 and then the proper product
under
that name and finally pull the mtd col D result for that product and
name.

Thanks in advance!
Denise

 

[denise]

Biff,

I appreciate you taking the time to explain how it works. I'll study the
explanations. I'm sure they will make my work more efficient. Thanks! -
Denise

to answer your question, no, the dynamic ranges were not the same size.
Should they always be?

For use in this type of formula where you're referencing 2 different ranges,
yes.

could you explain how the COLUMNS ($A:A)-1 part works?

It increments the Match location as the formula is copied across a row.

Based on your sample data:

=INDEX(D18,MATCH("Joe",A1:A8,0))

Evaluates to:

=INDEX(D18,1))

And returns the value in D1.

Since there is only one instance of the look_up value, Joe, we need a means
to find all the values associated with Joe and since those values are in a
contiguous range we can simply increment the Match value. As you copy across
the COLUMNS function will do that incrementing for us:

=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:A)-1) = D1
=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:B)-1) = D2
=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:C)-1) = D3

And those evaluate to:

=INDEX(D18,1+0) = D1
=INDEX(D18,1+1) = D2
=INDEX(D18,1+2) = D3

Biff

Thanks Lance and Biff, both of these methods work!

Biff, I didn't get a chance to reply to you earlier but to answer your
question, no, the dynamic ranges were not the same size. Should they
always
be? I did take your suggestion to just expand the cell references beyond
what
I would ever need.

If you have a moment, could you explain how the COLUMNS ($A:A)-1 part
works?

Thanks,
Denise

Hi!

(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D
is
dynamic range 'mtd')

Are you sure all of these ranges are evaluating to the same size?

It's pretty simple using just normal references:

=INDEX([Workbook1.xls]Sheet1!$D$1:$D$8,MATCH($A2,[Workbook1.xls]Sheet1!$A$1:$A$8,0)+COLUMNS($A:A)-1)

Copy across then down.

If you don't know how much data will be in the range just use an
arbitrary
guess that you will never exceed like D11000.

Biff

Hello,

This is a wonderful discussion group, always so helpful. I'm hoping
someone
will be able to help me with a problem I'm having trying to set up a
new
workbook to pull selected results from an existing one. I am trying to
figure
out the formula/function for cells B23 on workbook2:

EXISTING WORKBOOK1:
(Col A is dynamic range 'name', Col B is dynamic range 'product', Col D
is
dynamic range 'mtd')
A B C D
Joe P1 7
P2 1
P3 2
Total 10
Jill P1 3
P2 4
P3 1
Total 8

NEW WORKBOOK2
A B C D
P1 P2 P3
Joe 7 1 2
Jill 3 4 1

I entered the following as an array formula in cell B2 and it works
fine
to
return the proper result of 7:
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
This does not work to return the results for P2 or P3, I am assuming
because
I need some sort of an offset for the different rows in workbook1.

I am hoping someone can point me in the right direction here. First I
want
to locate the proper name in workbook 1 and then the proper product
under
that name and finally pull the mtd col D result for that product and
name.

Thanks in advance!
Denise

 

[Biff]

You're welcome!

Biff

Biff,

I appreciate you taking the time to explain how it works. I'll study the
explanations. I'm sure they will make my work more efficient. Thanks! -
Denise

to answer your question, no, the dynamic ranges were not the same size.
Should they always be?

For use in this type of formula where you're referencing 2 different
ranges,
yes.

could you explain how the COLUMNS ($A:A)-1 part works?

It increments the Match location as the formula is copied across a row.

Based on your sample data:

=INDEX(D18,MATCH("Joe",A1:A8,0))

Evaluates to:

=INDEX(D18,1))

And returns the value in D1.

Since there is only one instance of the look_up value, Joe, we need a
means
to find all the values associated with Joe and since those values are in
a
contiguous range we can simply increment the Match value. As you copy
across
the COLUMNS function will do that incrementing for us:

=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:A)-1) = D1
=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:B)-1) = D2
=INDEX(D18,MATCH("Joe",A1:A8,0)+COLUMNS($A:C)-1) = D3

And those evaluate to:

=INDEX(D18,1+0) = D1
=INDEX(D18,1+1) = D2
=INDEX(D18,1+2) = D3

Biff

Thanks Lance and Biff, both of these methods work!

Biff, I didn't get a chance to reply to you earlier but to answer your
question, no, the dynamic ranges were not the same size. Should they
always
be? I did take your suggestion to just expand the cell references
beyond
what
I would ever need.

If you have a moment, could you explain how the COLUMNS ($A:A)-1 part
works?

Thanks,
Denise


:

Hi!

(Col A is dynamic range 'name', Col B is dynamic range 'product', Col
D
is
dynamic range 'mtd')

Are you sure all of these ranges are evaluating to the same size?

It's pretty simple using just normal references:

=INDEX([Workbook1.xls]Sheet1!$D$1:$D$8,MATCH($A2,[Workbook1.xls]Sheet1!$A$1:$A$8,0)+COLUMNS($A:A)-1)

Copy across then down.

If you don't know how much data will be in the range just use an
arbitrary
guess that you will never exceed like D11000.

Biff

Hello,

This is a wonderful discussion group, always so helpful. I'm hoping
someone
will be able to help me with a problem I'm having trying to set up a
new
workbook to pull selected results from an existing one. I am trying
to
figure
out the formula/function for cells B23 on workbook2:

EXISTING WORKBOOK1:
(Col A is dynamic range 'name', Col B is dynamic range 'product',
Col D
is
dynamic range 'mtd')
A B C D
Joe P1 7
P2 1
P3 2
Total 10
Jill P1 3
P2 4
P3 1
Total 8

NEW WORKBOOK2
A B C D
P1 P2 P3
Joe 7 1 2
Jill 3 4 1

I entered the following as an array formula in cell B2 and it works
fine
to
return the proper result of 7:
=INDEX('workbook1.xls'!mtd,MATCH("Joe"&"P1",'workbook1.xls'!name&'workbook1.xls'!product,0))
This does not work to return the results for P2 or P3, I am assuming
because
I need some sort of an offset for the different rows in workbook1.

I am hoping someone can point me in the right direction here. First
I
want
to locate the proper name in workbook 1 and then the proper product
under
that name and finally pull the mtd col D result for that product and
name.

Thanks in advance!
Denise