Vlookups in a Pivot table brining back OFFSET Data

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?

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.

tlk40us wrote:
> 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?


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

Thank you, this worked great!

"Debra Dalgleish" wrote:

> 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.
>
> tlk40us wrote:
> > 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?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>