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]

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

b1: =Year(c1)&"|"&month(c1) <---Drop this down the data set.
d1: =vlookup(year(today())&"|"&month(today()),b1:d16,3,0)

^^^Try this (it should return "10")

If you want the user to be able to choose the year/month you can easily do it.

 

[Robert Mika]

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)

This- I hope you mean my last post returns all values that match current month.
We the setup you have posted later and unless the data are formatted as month/year (not a Text) you can try this:

Excel 2010
	B	C	D	E	F
1				Jun-12
2	Jun-12	10			10
3	May-12	3
4	Apr-12	3
5	Mar-12	2
6	Feb-12	0
7	Jan-12	4
8	Dec-12	0
9	Nov-12	6
10	Oct-12	4
11	Sep-12	2
12	Aug-12	2
13	Jul-12	1
14	Jun-11	2
15	May-11	0
16	Mar-11	5
17	Feb-11	5
Sheet1

Cell Formulas
Range	Formula
E1	=TODAY()
F2	=SUMPRODUCT(--(MONTH($B$2:$B$17)=MONTH(E1)),--(YEAR($B$2:$B$17)=YEAR(E1))*($C$2:$C$17))

 

[jackdiamond]

I think that has done the trick, thank you

 

[jackdiamond]

I have just realised that when my boss looks at the data it will be the next mont, so he will be for example looking at June's data in July!

Is there anyway of changing the formula that I used from post #12 so it looks up the previous month to the one we are actually in!!?

Apologies for changing the goal posts!

 

[Robert Mika]

Try this.
Two methods(the second is the one you should go with as SUMPRODUCT is rather used for calcualtion)
Excel 2010

	A	B	C	D	E	F
1				12-Jun
2					3	3
3	12-Jun	10
4	12-May	3
5	12-Apr	3
6	12-Mar	2
7	12-Feb	0
8	12-Jan	4
9	12-Dec	0
10	12-Nov	6
11	12-Oct	4
12	12-Sep	2
13	12-Aug	2
14	12-Jul	1
15	11-Jun	2
16	11-May	0
17	11-Mar	5
18	11-Feb	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT(--(MONTH($A$2:$A$18)=MONTH(D1)-1),--(YEAR($A$2:$A$18)=YEAR(D1))*($B$2:$B$18)) F2 =INDEX($B$3:$B$18,MATCH(TRUE,INDEX(YEAR($A$3:$A$18)&MONTH(A3:A18)=YEAR(D1)&MONTH(D1)-1,),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>