get the cell value from the range of dates

[Anju Vedula]

Hi,

I am looking for a formula which picks the current month and year from the range of dates and picks the corresponding row value from the range. For example, I have

A B C
2 October 5, 2013 10
3 November 5, 2013 15
4 December 5, 2013 18
5 January 5, 2014 23
6 February 5, 2014 24

Now, what I want is, if we are in the month of January 2014 I want 23 and If I am in December 2013 I want 18. I tried this formula, but I am getting error. I know that I am doing wrong, but I can't figure the correct formula.

=VLOOKUP(TEXT(now(), "mmm-yy"), TEXT(if(B2:B6, "<"&now(), B2:B6), "mmm-yy"), C2:C6)

 

[Luke M]

How about:
=SUMPRODUCT((TEXT(TODAY(),"mmyyyy")=TEXT(B2:B6,"mmyyyy"))*(C2:C6))
This assumes that there are no duplicate months in the same year listed in the col B range.

 

[Anju Vedula]

no, this is not working for me. It gives an error saying "Range has no entry corresponding to this cell". I found one alternate by creating a column with mmm-yy value of A2 to A6 and checking the value VLOOKUP(TEXT(now(), "mmm-yy"), B2:C6, 2, false) where B col is the mmm-yy format of A col and C is the actual value which I am looking for.

 

[Luke M]

I'm not sure why it's not working for you...works okay in example book. Your alternate solution works as well. Only thing I would suggest is using TODAY instead of NOW, as you just need to reclculate every day, not every second.

 

[Anju Vedula]

oh, that's a great idea. I will use today() instead of now(). Thanks a ton Luke.

One more thing I just wanted to tell you is, I am using Google Docs, not the Excel. This might be different as I observed that in google excel we don't have LOOKUP function, we have only VLOOKUP and HLOOKUP.

 

[bobhc]

observed that in google excel we don't

google excel!!!!!!!!!!!!

Lets hope the redmond guys don't read that....,