Lookup within a look up

[Bigslim]

Here is what I have. A Calendar with multiple cells per each date. what I am looking for is to present my data on a home row. without having to scroll each time to look for my data. I have already a cell presenting todays date at all times so when it changes the data will change accordingly. so I have a simple 1 month calendar, on top row says month and year, next row all the days(sun-sat) then each day has 2 col. of 5 rows. cell (1,1) has the date which is formatted as a special number "d" to put day from m/d/yyyy in that cell. all other cells are currently manual entry which I would like to pull the data from. I figured that I want something to the effect of Index and Match Function but not sure how it would be written. so in a sense I want to lookup the day then lookup the data within that day. sorry for not displaying anything, but the site wont let me go to next line

 

[Misca]

I'd do this with OFFSET-function.

First get the number of rows from the first calendar cell to get the first row:
First Row = FLOOR((Lookup Date- First Calendar Date)/7,1)*Rows tall (Rows tall = number of rows you have in your calendar for a single day)

Then get the number of columns from the first calendar cell to the first column:
First Col = MOD((DATE(YEAR($L$11),MONTH($L$11),$G$4)-$F$13),7)*Columns Wide (Columns Wide = number of columns you have in your calendar for a single day)

Add the First Row and First Col formulas to your worksheet for further use. Then use the OFFSET-function to get the value of that cell:
=OFFSET(Starting cell, First Row, First Col)

Or put the OFFSET-function inside an IF-function that checks if the returned value is 0 and displays nothing ("") if the formula returns a zero (The formula returns a zero if the cell is empty):
=IF(OFFSET(Starting cell, First Row, First Col)=0,"",OFFSET(Starting cell, First Row, First Col))

For the next rows & columns you need to get +1 to the First Row and First Col values. The easiest way to do this automatically when you drag the formula right / down is using the ROWS- and COLUMNS-functions.

The problem with the formula approach is that if your text does not fit the cell you don't see the whole text. To solve this you might want to use the Camera tool, which makes the whole thing really easy (+ you'll get to use shapes & images in your calendar cells as well:

First you might need to add the Camera tool to your Quick Access Toolbar by selecting Excel Options / Quick Access Toolbar / Commands Not In Ribbon, then locate the camera from the dropdown and add.

Once you have added the Camera tool to your Toolbar select a range from your worksheet, press the Camera tool and drag a shape to your workbook. Then click the shape you just added. You'll see the address of your selection in your formula row. You need to change this to get the camera select the day you have selected. Unfortunately the Camera tool does not support the OFFSET-function directly but it does support Named Ranges so go to your Formula tab and choose the Name Manager from there. Then add a named range "MyDateRng":
MyDateRng = OFFSET(First Cell of the calendar, First Row, First Col, Rows tall, Columns Wide)

Select the First cell of the calendar, the First Row-cell and First Col-cells with your mouse to get the sheet references right. The Rows Tall and Columns Wide values you can add by typing the values directly to the function if you're not going to change the size of your calendar days.

Once you have added the MyDateRng to your name manager your're good to go: Selecet the camera shape and change the current range address with =MyDateRng and you're done!