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?

Vlookups in a Pivot table brining back OFFSET Data
 

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

Vlookups in a Pivot table brining back OFFSET Data
 

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