Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OLAP Pivot table - How to show items with no data ? | Excel Worksheet Functions | |||
external import of multiple worksheet data & create pivot table | Excel Worksheet Functions | |||
Pivot Table for survey data w/ questions as Rows & poss answrs as | Excel Discussion (Misc queries) | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) |