Nested hlookup/vlookup (possibly just an if statement though?!?)

[mike_vr]

Hi there
Have two sets of data on two different worksheets as such:

1st set:
Week Day Date UK Pages
1 Mon 05/10/2009 80
1 Tue 06/10/2009 80
1 Wed 07/10/2009 96
1 Thu 08/10/2009 88
1 Fri 09/10/2009 104
1 Sat 10/10/2009 120
2 Mon 12/10/2009 80
2 Tue 13/10/2009 80
2 Wed 14/10/2009 96
2 Thu 15/10/2009 96
2 Fri 16/10/2009 104
2 Sat 17/10/2009 120
3 Mon 19/10/2009 80
3 Tue 20/10/2009 80
3 Wed 21/10/2009 80
3 Thu 22/10/2009 96
3 Fri 23/10/2009 104
3 Sat 24/10/2009 120

2nd set:
WEEK MON TUE WED THU FRI SAT
1 80 80 96 88 104 120
2 80 80 96 96 104 120
3 80 80 80 96 104 120

I'm trying to get the horizontal 2nd set figures into the vertical column of
the 1st set under UK pages.
So far I have this:
=IF(VLOOKUP(A3,Sheet2!$A$5:$A$56,1,FALSE),HLOOKUP(Sheet1!B3,Sheet2!$D$4:$I$56,2,FALSE),0)

This returns the correct value, but as I copy the formula down for week 2 I
have to manually update the hlookup row number by one for each new week. I
have plenty of weeks to do and this is going to take ages.
Is there a shortcut?
Please let me know if you need any more info, any help is greatly appreciated!
Cheers
Mike

 

[Luke M]

Why not replace the row callout with cell reference? Since week 1 is in row
2, we can do A3+1.

=IF(VLOOKUP(A3,Sheet2!$A$5:$A$56,1,FALSE),HLOOKUP(Sheet1!B3,Sheet2!$D$4:$I$56,A3+1,FALSE),0)