Vlookups in a Pivot table brining back OFFSET Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tlk40us - 18 Mar 2006 19:48 GMT

I have a single page summary report that obtains specific information from a
pivot table. I would like to bring back company leaders within designated
groups found by a lookup table. The company and branch leaders are always
dynamic. Since the company leader data is is always OFFSET over a column and
down a line, how can I build a lookup that finds a region, "Specific Name"
then select the company leader by the OFFSET?

This formula does not work, however it was the direction I was working on.
=VLOOKUP(A2,group,OFFSET(A6,1,1),FALSE) The OFFSET is buried in the eqaution
the same as a MATCH or an INDEX. How can I bring back data first prior to
OFFSETting the needed information?

Reply to this Message

Debra Dalgleish - 18 Mar 2006 22:02 GMT

You can use MATCH to return the row where the region name is found, and
offset by that number of rows:

=OFFSET($A$1,MATCH(H2,A:A,0),1)

where Region in the pivot table is in column A, and the specific region
is in cell H2.

> I have a single page summary report that obtains specific information from a
> pivot table. I would like to bring back company leaders within designated
[quoted text clipped - 7 lines]
> the same as a MATCH or an INDEX. How can I bring back data first prior to
> OFFSETting the needed information?

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

tlk40us - 20 Mar 2006 18:52 GMT

Thank you, this worked great!

> You can use MATCH to return the row where the region name is found, and
> offset by that number of rows:
[quoted text clipped - 15 lines]
> > the same as a MATCH or an INDEX. How can I bring back data first prior to
> > OFFSETting the needed information?

Reply to this Message