Dates and look ups?

[jackdiamond]

Hi All,

I am hoping to look up some data from another worksheet which changes as today's date changes.

So for example, if it is June, I want it to look up the data that shows for June. If it is July today, I want it to look up the data only from July in my spreadsheet etc etc.

This is my current formula =VLOOKUP(D5,$B$25:$C$39,2,FALSE). This is before I wanted it to show the same month as we are currently in

Any help would be much appreciated

Ta
Jack

 

[pplstuff]

Is your June data in a single cell? Or, are there a range of cells with values for June (and you essentially want to pull everything from June)?

If it's the former, try:
=vlookup(month(today()),$b$25....)

Depending on how your dates are formatted, maybe:
=vlookup(text(today(),"mmm"),$b$25...)

With sample data, we could give a more accurate formula.

 

[jackdiamond]

The June data is in a single cell.

Basically, I collate a list of data in one worksheet that has month in one column, then the numbers in the next column.

My boss only wants to see the numbers for the relevant month which will be viewed on a seperate worksheet.

Does this make sense?

 

[pplstuff]

I think it makes sense.

How is June formatted? "6", "Jun", "June", "26-JUN", 6/26/12?

 

[jackdiamond]

Jun-12

 

[Robert Mika]

Not sure if Jun-12 is text or just formatting,but if is formatting try this:

Sheet4

*	A	B	C	D	E	F
1	date	data	*	*	*	June
2	02-May	29	*	*	3	9
3	02-May	78	*	*	*	54
4	04-May	52	*	*	*	85
5	05-May	7	*	*	*	*
6	05-May	77	*	*	*	*
7	06-May	57	*	*	*	*
8	07-May	87	*	*	*	*
9	08-May	98	*	*	*	*
10	09-May	56	*	*	*	*
11	10-May	4	*	*	*	*
12	11-May	73	*	*	*	*
13	12-May	4	*	*	*	*
14	13-May	7	*	*	*	*
15	14-May	80	*	*	*	*
16	15-May	99	*	*	*	*
17	16-May	84	*	*	*	*
18	17-May	46	*	*	*	*
19	18-May	86	*	*	*	*
20	19-May	71	*	*	*	*
21	20-May	39	*	*	*	*
22	21-May	27	*	*	*	*
23	22-May	60	*	*	*	*
24	23-May	67	*	*	*	*
25	24-May	41	*	*	*	*
26	25-May	80	*	*	*	*
27	26-May	18	*	*	*	*
28	27-May	4	*	*	*	*
29	28-May	2	*	*	*	*
30	29-May	42	*	*	*	*
31	30-May	45	*	*	*	*
32	31-May	31	*	*	*	*
33	01-Jun	9	*	*	*	*
34	02-Jun	54	*	*	*	*
35	03-Jun	85	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:93px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F1 =TODAY() E2 =SUMPRODUCT(--(MONTH($A$2:$A$35)=MONTH(TODAY()))) F2 {=IF(ROWS($F$2:F2)<=$E$2,INDEX(B$2:B$35,SMALL(IF(MONTH($A$2:$A$35)=MONTH(TODAY()),ROW($A$2:$A$35)-ROW($E$2)+1),ROWS($F$2:F2))),"")} F3 {=IF(ROWS($F$2:F3)<=$E$2,INDEX(B$2:B$35,SMALL(IF(MONTH($A$2:$A$35)=MONTH(TODAY()),ROW($A$2:$A$35)-ROW($E$2)+1),ROWS($F$2:F3))),"")} F4 {=IF(ROWS($F$2:F4)<=$E$2,INDEX(B$2:B$35,SMALL(IF(MONTH($A$2:$A$35)=MONTH(TODAY()),ROW($A$2:$A$35)-ROW($E$2)+1),ROWS($F$2:F4))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[pplstuff]

You could add another column and make it a month only column, or use index(match())

=index($b$25:$c:$39,match(month(today()),month($b$25:$b$39),0),2)

Try that, if it gives you an error then try it as an array (hit ctrl+shift+enter)

NOTE*** Today() is only in excel 2007 or 2010

 

[jackdiamond]

C D
Jun-12 10
May-12 3
Apr-12 3
Mar-12 2
Feb-12 0
Jan-12 4
Dec-11 0
Nov-11 6
Oct-11 4
Sep-11 2
Aug-11 2
Jul-11 1
Jun-11 2
May-11 0
Mar-11 5
Feb-11 5

This is what I have. Is there an easy way to do what I want? That previous post has confused the hell out of me ;o)

 

[Robert Mika]

NOTE*** Today() is only in excel 2007 or 2010

Not sure but I found this:
http://www.techonthenet.com/excel/formulas/today.php

 

[jackdiamond]

ok, this returns data as long as it is an exact date but I want it to look up any date in the current month?

=VLOOKUP(A1,D4:E19,2,FALSE)