Complex Lookup Formula

[todd.huttenstine]

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_Categories". 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

 

[T. Valko]

I love complex lookups!

See if this sample file helps:

http://cjoint.com/?jwfoBzUh4H

Sheet 2 contains simulated pivot tables.

 

[todd.huttenstine]

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

 

[T. Valko]

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.