Lookup in Excel 2010

Topic

New Reply

I have attached a spreadsheet with two tabs. Tab 1 contains weekly sheets with data that is totaled at the bottom of the day columns. The weekly sections are not linear, the weeks are stacked on top of each other. Tab 2 contains a sheet that has the day of the month at the top of the column (these will actually be hidden once the sheet is complete. I have hi-lighted the fields that I want to fill in Tab 2 with the hi-lighted field in Tab 1. Is there a way to auto-fill the data in Tab 2 once the data is entered in Tab 1, without having to just manually use = this cell. Is there a Lookup function that will look at the date in Tab 2, then find the date in Tab 1 and return the value of the field 10 cells below that date?

Reply | Quote

Replies

I can’t see the highlighted fields you mention.
Your tab2 sheet dates don’t correspond to any of the dates in tab1 sheet.
Can you check your posted file again.

zeddy

Reply | Quote

Sorry the yellow hi-ligthing of the cells did come across on the download. Should have put cell references anyway. Here they are.
In Tab 2, Cell C3 is the cell that I want populated automatically, by using the date in C2. Find that date in Tab 1 (in this case it appears in C3) and return the figure in the cell that is 10 rows below (cell C13). I have also sent an updated sheet.

Reply | Quote

Hi

Need a little more info:
Q1: Will there be lots and lots of weekly sections on sheet [Tab 1] or will there always be just 4??

Q2: On sheet [Tab 2], will cell [B2] always be the same value as cell [C3] on sheet [Tab 1]??

zeddy

Reply | Quote

In Tab2!C3 enter the mega formula:

=OFFSET(‘Tab 1′!$A$1,IF(ISNUMBER(MATCH(F2,’Tab 1′!$C:$C,0)),MATCH(F2,’Tab 1′!$C:$C,0),0)+IF(ISNUMBER(MATCH(F2,’Tab 1′!$D:$D,0)),MATCH(F2,’Tab 1′!$D:$D,0),0)+IF(ISNUMBER(MATCH(F2,’Tab 1′!$E:$E,0)),MATCH(F2,’Tab 1′!$E:$E,0),0)+IF(ISNUMBER(MATCH(F2,’Tab 1′!$F:$F,0)),MATCH(F2,’Tab 1′!$F:$F,0),0)+IF(ISNUMBER(MATCH(F2,’Tab 1′!$G:$G,0)),MATCH(F2,’Tab 1′!$G:$G,0),0)+IF(ISNUMBER(MATCH(F2,’Tab 1′!$H:$H,0)),MATCH(F2,’Tab 1′!$H:$H,0),0)+IF(ISNUMBER(MATCH(F2,’Tab 1′!$I:$I,0)),MATCH(F2,’Tab 1′!$I:$I,0),0)+9,ISNUMBER(MATCH(F2,’Tab 1′!$C:$C,0))*COLUMN($C1)+ISNUMBER(MATCH(F2,’Tab 1′!$D:$D,0))*COLUMN($D1)+ISNUMBER(MATCH(F2,’Tab 1′!$E:$E,0))*COLUMN($E1)+ISNUMBER(MATCH(F2,’Tab 1′!$F:$F,0))*COLUMN($F1)+ISNUMBER(MATCH(F2,’Tab 1′!$G:$G,0))*COLUMN($G1)+ISNUMBER(MATCH(F2,’Tab 1′!$H:$H,0))*COLUMN($H1)+ISNUMBER(MATCH(F2,’Tab 1’!$I:$I,0))*COLUMN($I1)-1)

Copy it into the F, I, L, O13, etc

Steve

Reply | Quote

Hi

You could use a shorter formula in Tab2C3:
=IFERROR(OFFSET(‘Tab 1’!$A$1,(12*(INT((C2-‘Tab 1’!$C$3)/7)+1)),MOD((C2-‘Tab 1’!$C$3),7)+2),0)

..and copy to other cells as required (including [C5], [E5] etc)

This will work provided the week blocks on sheet [Tab 1] are consistent in size and spacing, and that the dates in each block are sequential starting from whatever date is in cell [C3] on sheet [Tab 1].
The error check in the formula simply returns a zero value if the date you are checking is earlier than the first date in cell [C3] on sheet [Tab 1]

My formula eliminates the matching of dates, and instead, relies on the structure of the weekly blocks.
My reason for avoiding using match with dates is essentially that Excel stores dates as a number e.g. today 29-Jan-2012 is 40937. Dates for this year 2012 fall in the range 40909 to 41274. So if you searched a column for todays date you would hope that there weren’t any numbers like 40937 in the range that were data items etc.

zeddy

Reply | Quote

A much better solution provided that the data is set up in the pattern. I also chose to NOT use the IFERROR in case the solution needed to be compatible with older (pre-XL2007) XLS versions. IFERROR was not introduced until XL2007

Steve

Reply | Quote

Sorry so long since responding – I thought I had responded weeks ago, eveything worked fine and yes there was a pattern for a total of 53 weeks, for the whole year. Thanks again for your help.

Reply | Quote