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 |
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 |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com