Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested hlookup/vlookup (possibly just an if statement though?!?)
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested hlookup/vlookup (possibly just an if statement though?!?)
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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "mike_vr" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF statement with VLOOKUP | Excel Discussion (Misc queries) | |||
Retry: VLOOKUP nested in IF Statement | Excel Worksheet Functions | |||
Rounding criteria within a nested vlookup and hlookup | Excel Worksheet Functions | |||
Problem with data using IF and Nested IF statements possibly??? | Excel Discussion (Misc queries) | |||
If Statement possibly? | Excel Worksheet Functions |