Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup data in a variable table & retrieve data from a pivot table
I am trying to lookup data from two concatenated colums and retrieve data
from a pivot table where the column & row matches the concatenation, return the max value in the pivot table. Below is my example data: Pivot Table Max of Value Column Row Total AB_N AB AL 1.25 AR 1.15 AZ 1.62 BC CA 1.25 CO 1.95 AL_N AB 1.95 AL 3 AR 1.75 AZ 1.65 AL_S AB 1.95 AL 3 AR 1.75 AZ 1.65 I want to retreive the max value where the concatenation of the below table matches matches the column and row in the below table. Table O Region D State Concatenation AR_N FL AR_NFL PA_E GA PA_EGA VA_E WA VA_EWA VA_E TX VA_ETX VA_E FL VA_EFL Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup data in a variable table & retrieve data from a pivot table
Assume the pivot's data rows are in A3:C3 down
In D3: =A3 In D4: =IF(A4="",D3,A4) Copy D4 down as far as required This quickly creates the required "filled" helper col (for col A) which remains dynamic to what's in the pivot's col A Then, assuming the region & state paired inputs are listed in F3:G3 down, eg: AL_N AL AB_N AZ AL_S AR etc (the concat col can be dropped) Just place this in H3, normal ENTER, then copy down: =INDEX($C$3:$C$100, MATCH(1,INDEX(($D$3:$D$100=F3)*($B$3:$B$100=G3),), 0)) to return the required max values from the pivot's col C Adapt the ranges to suit the max expected extent -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Shawna" wrote: I am trying to lookup data from two concatenated colums and retrieve data from a pivot table where the column & row matches the concatenation, return the max value in the pivot table. Below is my example data: Pivot Table Max of Value Column Row Total AB_N AB AL 1.25 AR 1.15 AZ 1.62 BC CA 1.25 CO 1.95 AL_N AB 1.95 AL 3 AR 1.75 AZ 1.65 AL_S AB 1.95 AL 3 AR 1.75 AZ 1.65 I want to retreive the max value where the concatenation of the below table matches matches the column and row in the below table. Table O Region D State Concatenation AR_N FL AR_NFL PA_E GA PA_EGA VA_E WA VA_EWA VA_E TX VA_ETX VA_E FL VA_EFL |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup data in a variable table & retrieve data from a pivot t
Thanks Max! This is exactly what I was looking for. I figured I was making
it more complex than it really needed to be. Thanks again! "Max" wrote: Assume the pivot's data rows are in A3:C3 down In D3: =A3 In D4: =IF(A4="",D3,A4) Copy D4 down as far as required This quickly creates the required "filled" helper col (for col A) which remains dynamic to what's in the pivot's col A Then, assuming the region & state paired inputs are listed in F3:G3 down, eg: AL_N AL AB_N AZ AL_S AR etc (the concat col can be dropped) Just place this in H3, normal ENTER, then copy down: =INDEX($C$3:$C$100, MATCH(1,INDEX(($D$3:$D$100=F3)*($B$3:$B$100=G3),), 0)) to return the required max values from the pivot's col C Adapt the ranges to suit the max expected extent -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Shawna" wrote: I am trying to lookup data from two concatenated colums and retrieve data from a pivot table where the column & row matches the concatenation, return the max value in the pivot table. Below is my example data: Pivot Table Max of Value Column Row Total AB_N AB AL 1.25 AR 1.15 AZ 1.62 BC CA 1.25 CO 1.95 AL_N AB 1.95 AL 3 AR 1.75 AZ 1.65 AL_S AB 1.95 AL 3 AR 1.75 AZ 1.65 I want to retreive the max value where the concatenation of the below table matches matches the column and row in the below table. Table O Region D State Concatenation AR_N FL AR_NFL PA_E GA PA_EGA VA_E WA VA_EWA VA_E TX VA_ETX VA_E FL VA_EFL |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup data in a variable table & retrieve data from a pivot t
Delighted it helped. My pleasure, Shawna.
-- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Shawna" wrote in message ... Thanks Max! This is exactly what I was looking for. I figured I was making it more complex than it really needed to be. Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using 2 cells in a table to retrieve data from another table | Excel Worksheet Functions | |||
Convert Pivot table back to Data Table | Excel Discussion (Misc queries) | |||
Best method to retrieve table data | Excel Worksheet Functions | |||
HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE | New Users to Excel | |||
Excel Pivot Table Plugin? (crosstab to data table) | Excel Discussion (Misc queries) |