Thursday, April 7, 2011

yogi_query cell Value by Date according to today date

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

Placeb0 said:
I've got a table with three columns: Date (past and future, in several days difference between each value) and Data 1, like this:

Date Data1
01.04.2011 1.25
03.04.2011 2.58
07.04.2011 12.5
12.04.2011 5.6
15.04.2011 11.3

I need to filter/query in a separate sheet the value of Data1 according to current date, or last Data 1 values if the date in Date column is not equal to today date.
for example: if current date is 06.04.2011, i need it to return the Data1 value of 03.04.2011, and if current date becomes 07.04.2011, than to show the Data1 value of 07.04.2011


Well here we go ... in the following I used the FILTER function to extract the desired result in cell A1 of Sheet2. To alleviate the confusion of month or day numbering I used lettered month in the dates.

In Sheet3, I have used the QUERY function to extract the needed value in cell A1