Lookup

[wilsonbramos]

Hi,

I need some help. My table is structured similar to a calendar, for example, my column headers are the days from Sunday to Saturday, my row headers are the months from January to December, and the contents of my table are the days of the year from 1 to 365.

I need a formula such that when i lookup for a particular day of the year (example 3rd day or "3"), the formula would return the column header and/or the row header. In my example the formula should return "Monday" or "January". (Using 2011 calendar)

Thanks.

 

[marreco]

Poste your worksheet and send us one link
So that staff can help you

 

[T. Valko]

Hi,

I need some help. My table is structured similar to a calendar, for example, my column headers are the days from Sunday to Saturday, my row headers are the months from January to December, and the contents of my table are the days of the year from 1 to 365.

I need a formula such that when i lookup for a particular day of the year (example 3rd day or "3"), the formula would return the column header and/or the row header. In my example the formula should return "Monday" or "January". (Using 2011 calendar)

Thanks.

You don't need a lookup table to do this. You can just use the dates of the year.

Let's assume you enter a number in cell A1 from 1 to 366 (leap years!).

For the weekday...

=TEXT(DATE(2011,1,1)+A1-1,"dddd")

For the month...

=TEXT(DATE(2011,1,1)+A1-1,"mmmm")

 

[shift-del]

Hi

You don't need a big table to calculate that.
<table valign="middle" colspan="5" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="87pt"><col width="60pt"><col width="75,75pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="5" align="middle">Worksheet 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="middle">Year</td><td align="middle">Day of the year</td><td align="middle">Month</td><td align="middle">Weekday</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="middle">2011</td><td align="middle">3</td><td align="middle">January</td><td align="middle">Monday</td></tr></tbody></table>
<table valign="middle" colspan="3" style="color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(170, 238, 170);"><td>Cell</td><td>Format</td><td align="right">Value</td></tr><tr><td>C2</td><td>mmmm</td><td align="right">03.01.2011</td></tr><tr><td>D2</td><td>dddd</td><td align="right">03.01.2011</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=EOMONTH(DATE(A2,1,0),0)+B2</td></tr><tr><td>D2</td><td>=C2</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[wilsonbramos]

Hi,

My table is not actually a "Calendar", I just used it for better illustration.
Do you have other solutions? Thanks.

 

[T. Valko]

Hi,

My table is not actually a "Calendar", I just used it for better illustration.
Do you have other solutions? Thanks.

It depends on what your table looks like.

Can you post a snippet of it so we can see its layout?