Lookup Values

[Harlan]

I've gotten pretty good at VLOOKUP functions, figuring out how to lookup a
value in a different worksheet based on two values in the first worksheet.
Now I need to figure out how to look up a value in a different worksheet
based on three values in the first worksheet. I have a sheet that is going
to host all my data (accounting categories with corresponding account
numbers). I use the account numbers as the lookup value. The table is then
one of the sheets in the workbook, named for the year (2009, 2010, etc.).
Now here's where it gets tricky. I have to other values. The first is the
accounting period number, which is what I have been using in the past. Those
13 numbers are the first row across the top. The second number is going to
be the week in the period (1, 2, 3, or 4) . So I want to look for a value in
the 2009 sheet, for period 9, week 2. How would I go about doing this? Is
it even possible? For reference, here is the formula that I have been using

VLOOKUP(A10,INDIRECT("'"&$P$3&"'!$A$1:$P$165"),MATCH($P$4,INDIRECT("'"&$P$3&"'!$A$1:$O$1"),0),FALSE)

Where $P$3 is the year and $P$4 is the period.
$A$1:$P$165 is the table with all the values.
$A$1:$O$1 is the first row of the worksheet with the numbers 1-13.

Thanks in advance.

 

[Harlan Grove]

. . . I use the account numbers as the lookup value.  The table is then
one of the sheets in the workbook, named for the year (2009, 2010, etc.).  
Now here's where it gets tricky. I have to other values.  The first is the
accounting period number, which is what I have been using in the past.  Those
13 numbers are the first row across the top.  The second number is goingto
be the week in the period (1, 2, 3, or 4) .  So I want to look for a value in
the 2009 sheet, for period 9, week 2.  How would I go about doing this?  Is
it even possible?  For reference, here is the formula that I have been using

VLOOKUP(A10,INDIRECT("'"&$P$3&"'!$A$1:$P$165"),
MATCH($P$4,INDIRECT("'"&$P$3&"'!$A$1:$O$1"),0),FALSE)

Where $P$3 is the year and $P$4 is the period.  
$A$1:$P$165 is the table with all the values.
$A$1:$O$1 is the first row of the worksheet with the numbers 1-13.  

....

Looks like there could be a mistake in the 2nd argument to the MATCH
call. If A10 is an account number, that account number would be sought
in col A of the A1165 range, but you're also looking up the P4 value
in A1:O1. Do the rows 1 in the other worksheets contain column
headings? If so, the lookup table should be A2165.

That said, you haven't provided enough information. You mentioned that
Period was in row 1 in the year worksheets, but where is week? 4
weekly columns per period? Weeks in different rows?

If there were 4 columns for weeks within each period, so the table
contained at least 53 columns (1st col for account numbers/IDs, next 4
cols for weeks 1 to 4 in period 1, next 4 cols for weeks 1 to 4 in
period 2, etc.), then your formula could be simplified to

=VLOOKUP(A10,CHOOSE(MATCH($P$3,{2009;2010;2011;...}),
'2009'!$B$2:$BA$165,'2010'!$B$2:$BA$165,'2010'!$B$2:$BA$165,...),
4*(PeriodNumber-1)+WeekNumber,0)

Maybe the CHOOSE(MATCH(...),...) isn't a simplification, but it avoids
volatile INDIRECT calls. Since you're using relative and absolute
references, it looks like there may be a lot of these formulas. A lot
of formulas each calling volatile functions can really slow down
recalculation.

 

[Harlan]

The weeks, 1-4 are in row 2 in the Weekly Worksheet. So yes, there are 4
columns for the weeks, going from C2:BF2. What I don't understand in your
formula are the curly brackets. Also, I want to be able to enter the year in
a cell, the period in a cell and the week in a cell and then lookup based on
those values. If you noticed my formula doesn't contain any references to
2009, 2010, etc.

By the way, it is interesting that your name is Harlan too.

Thanks

 

[Harlan]

I'm going to be a little more precise, because I know that trying to explain
a workbook and worksheets in words can be confusing.
The new worksheet I have created is called W2009. Eventually there will be
W2010, W2011, etc. On the W2009 sheet, the first column (A) is the account
numbers starting in row 3, the second column (B) is the account names. After
that are the columns for the weeks of the periods, 4 columns for each period.
The first row (1) are the period numbers 1-13 and the second row is the week
numbers, 1-4.
In the 'Weekly' worksheet I will be entering values in O3, O4 and O5. O3
for the year. O4 for the period. And O5 for the week. The values that are
being looked up, account balances and such should change as the numbers in
those three cells change.
I hope this sheds a little more light on things.

Thanks

Harlan

 

[Harlan Grove]

The weeks, 1-4 are in row 2 in the Weekly Worksheet.  So yes, there are 4
columns for the weeks, going from C2:BF2. . . .

C2:BF2 is 56 columns. If there are 4 weeks per period, then there'd be
56/4 = 14 periods. You had said previously there were 13 periods. I
can see how having weeks begin on Sunday or Monday and years beginning
on 1 Jan and ending on 31 Dec you could have 53 weeks, but I'm at a
loss to figure out how you have 56 weeks.