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

"Biff" wrote:

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(D1:D8,MATCH("Joe",A1:A8,0))

Evaluates to:

=INDEX(D1:D8,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(D1:D8,MATCH("Joe",A1:A8,0)+COLUMNS($A:A)-1) = D1
=INDEX(D1:D8,MATCH("Joe",A1:A8,0)+COLUMNS($A:B)-1) = D2
=INDEX(D1:D8,MATCH("Joe",A1:A8,0)+COLUMNS($A:C)-1) = D3

And those evaluate to:

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

Biff

"denise" wrote in message
...
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


"Biff" wrote:

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 D1:D1000.

Biff

"denise" wrote in message
...
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 B2:D3 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",'workb ook1.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