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



"denise" wrote:

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


Joe P1 7
P2 1
P3 2


S/b

Joe P1 7
Joe P2 1
Joe P3 2


"denise" wrote:

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