Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup Formula
On sheet1 Column A starting in cell A2, I have over 200 stores and
each store has 5 cetegories(that are the same for each store) listed directly under the store name. Here is an example of the locations and categories: Cell A2 = "Store1" Cell A3="Cat1" Cell A4="Cat2" Cell A5="Cat3" Cell A6="Cat4" Cell A7="Cat5" Cell A9 = "Store2" Cell A10="Cat1" Cell A11="Cat2" Cell A12="Cat3" Cell A13="Cat4" Cell A14="Cat5" Cell A16 = "Store3" Cell A17="Cat1" Cell A18="Cat2" Cell A19="Cat3" Cell A20="Cat4" Cell A21="Cat5" And so on... Across the top in column 2 starting in cell B2 I have 12 Months (Jan to Dec). This spans from Column B to Column K. On a second sheet called Sheet2 I have other tables that I need to pull the appropriate cooresponding value from (Matching the store name, category, and month) Matching Criteria Number 1: Column A Contains 5 pivot tables (1 pivot table named after each of the 5 categories). The pivot tables can change in size so I will never know what cell the pivot tables will be in. They will always be in column A. The pivot table can be identified by a cell in Column A named "Sum of The_CATEGORY_NAME_of_1_of_the_5_possible_Categorie s". So for instance Cat1 pivot table can be found by finding the value "Sum of Cat1" somewhere in column A. All the pivot tables follow this logic. Matching Criteria Number 2: All 200+ stores will be listed in each pivot table in column A. This means that all of the 200+ stores will show up 5 times(because there are 5 pivot tables). You can tell where each pivot table ends with a value called "Grand Total" at the very bottom of the pivot table. This means the end of the range can be found by finding the first instance of "Grand Total" starting from the found pocition of the first "Sum of Cat". (The match function will always retrieve the 1st instance of the criteria) Matching Criteria Number 3(Not that big of deal): The Months will be listed accross horizontally starting 1 row down from the matching location of the "Sum of Cat" address. This is not really important because these months are in order from Jan to Dec and relative to the lookup table, so all I have to do is simply pull the formula over accross columns. Here is the formula I have come up with so far but it is not working because I am having to hard code in the A26 address and this will not work because I do not know the position of each pivot table. I am having a hard time getting the Match formula to see a dynamic Starting cell in the lookup_array. I use the 65536 because I am taking advtange of the fact that Match formula retunrs the 1st instance of the criteria so regardless of how many times its repeating in the range, it will pull back the first instance. =INDIRECT("'Dist 1'!" & ADDRESS(MATCH($A$51,INDIRECT(("'Dist 1'!" & "A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0) & ":A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+MATCH("Grand Total",'Dist 1'!$A26:$A $65536,0)-1)),0)+(MATCH("Sum of " & $A$52,'Dist 1'!$A:$A, 0)-1),MATCH(B51,INDIRECT("'Dist 1'!" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1) & ":" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1)),0))) I may be going about this incorrectly. If anyone can assist me with a new formula or modify mine to work, I will be greatlly appreciated. Thanks Todd |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup Formula
I love complex lookups!
See if this sample file helps: http://cjoint.com/?jwfoBzUh4H Sheet 2 contains simulated pivot tables. -- Biff Microsoft Excel MVP wrote in message s.com... On sheet1 Column A starting in cell A2, I have over 200 stores and each store has 5 cetegories(that are the same for each store) listed directly under the store name. Here is an example of the locations and categories: Cell A2 = "Store1" Cell A3="Cat1" Cell A4="Cat2" Cell A5="Cat3" Cell A6="Cat4" Cell A7="Cat5" Cell A9 = "Store2" Cell A10="Cat1" Cell A11="Cat2" Cell A12="Cat3" Cell A13="Cat4" Cell A14="Cat5" Cell A16 = "Store3" Cell A17="Cat1" Cell A18="Cat2" Cell A19="Cat3" Cell A20="Cat4" Cell A21="Cat5" And so on... Across the top in column 2 starting in cell B2 I have 12 Months (Jan to Dec). This spans from Column B to Column K. On a second sheet called Sheet2 I have other tables that I need to pull the appropriate cooresponding value from (Matching the store name, category, and month) Matching Criteria Number 1: Column A Contains 5 pivot tables (1 pivot table named after each of the 5 categories). The pivot tables can change in size so I will never know what cell the pivot tables will be in. They will always be in column A. The pivot table can be identified by a cell in Column A named "Sum of The_CATEGORY_NAME_of_1_of_the_5_possible_Categorie s". So for instance Cat1 pivot table can be found by finding the value "Sum of Cat1" somewhere in column A. All the pivot tables follow this logic. Matching Criteria Number 2: All 200+ stores will be listed in each pivot table in column A. This means that all of the 200+ stores will show up 5 times(because there are 5 pivot tables). You can tell where each pivot table ends with a value called "Grand Total" at the very bottom of the pivot table. This means the end of the range can be found by finding the first instance of "Grand Total" starting from the found pocition of the first "Sum of Cat". (The match function will always retrieve the 1st instance of the criteria) Matching Criteria Number 3(Not that big of deal): The Months will be listed accross horizontally starting 1 row down from the matching location of the "Sum of Cat" address. This is not really important because these months are in order from Jan to Dec and relative to the lookup table, so all I have to do is simply pull the formula over accross columns. Here is the formula I have come up with so far but it is not working because I am having to hard code in the A26 address and this will not work because I do not know the position of each pivot table. I am having a hard time getting the Match formula to see a dynamic Starting cell in the lookup_array. I use the 65536 because I am taking advtange of the fact that Match formula retunrs the 1st instance of the criteria so regardless of how many times its repeating in the range, it will pull back the first instance. =INDIRECT("'Dist 1'!" & ADDRESS(MATCH($A$51,INDIRECT(("'Dist 1'!" & "A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0) & ":A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+MATCH("Grand Total",'Dist 1'!$A26:$A $65536,0)-1)),0)+(MATCH("Sum of " & $A$52,'Dist 1'!$A:$A, 0)-1),MATCH(B51,INDIRECT("'Dist 1'!" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1) & ":" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1)),0))) I may be going about this incorrectly. If anyone can assist me with a new formula or modify mine to work, I will be greatlly appreciated. Thanks Todd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup Formula
T. Valko,
Thanks! Can you explain the logic of this formula? Also, I was able to fix mine, however mine is much longer. Yours is shorter and appears to be working so that seems to be the best one, I just cant understand it. =INDIRECT("'Dist 1'!" & ADDRESS(MATCH($A$51,INDIRECT(("'Dist 1'!" & "A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0) & ":A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+MATCH("Grand Total",INDIRECT("'Dist 1'! $A"&(MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)) & ":$A$65536"),0)-1)), 0)+(MATCH("Sum of " & $A$52,'Dist 1'!$A:$A, 0)-1),MATCH(B51,INDIRECT("'Dist 1'!" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1) & ":" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1)), 0))) Thanks Todd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup Formula
Can you explain the logic of this formula?
=OFFSET(Sheet2!$A$1,MATCH("*"&$A3&"*",Sheet2!$A$1 :$A$17,0)-1+MID($A$2,6,100),COLUMNS($B2:B2)) We use this to find what row the Cat number is on: MATCH("*"&$A3&"*",Sheet2!$A$1:$A$17,0)-1 After we know where the Cat number is we then need to adjust that row number to find the Store number. I assumed the store numbers would be listed in the pivots in ascending order so Store 1 is always 1 row under "Sum of Cat n". Store 2 is always 2 rows under "Sum of Cat n". Store 3 is always 3 rows under "Sum of Cat n", etc, etc. So the adjustment is simply adding the store number to: MATCH("*"&$A3&"*",Sheet2!$A$1:$A$17,0)-1 MATCH("*"&$A3&"*",Sheet2!$A$1:$A$17,0)-1+MID($A$2,6,100) When you copy the formula across a row COLUMNS($B2:B2) will increment by 1 pulling the correct month column. -- Biff Microsoft Excel MVP wrote in message ups.com... T. Valko, Thanks! Can you explain the logic of this formula? Also, I was able to fix mine, however mine is much longer. Yours is shorter and appears to be working so that seems to be the best one, I just cant understand it. =INDIRECT("'Dist 1'!" & ADDRESS(MATCH($A$51,INDIRECT(("'Dist 1'!" & "A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0) & ":A" & MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+MATCH("Grand Total",INDIRECT("'Dist 1'! $A"&(MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)) & ":$A$65536"),0)-1)), 0)+(MATCH("Sum of " & $A$52,'Dist 1'!$A:$A, 0)-1),MATCH(B51,INDIRECT("'Dist 1'!" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1) & ":" & (MATCH("Sum of " & $A$52,'Dist 1'!$A:$A,0)+1)), 0))) Thanks Todd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Lookup question. | Excel Worksheet Functions | |||
Complex Lookup | Excel Discussion (Misc queries) | |||
Complex Lookup | Excel Worksheet Functions | |||
Complex lookup | Excel Worksheet Functions | |||
complex lookup | Excel Discussion (Misc queries) |