Re: [libreoffice-users] Calk - lookup cell in column

On 09/14/2013 07:01 PM, Tim Deaton wrote:
> The closest I came to a solution was a scenario where all the dates 
> for the year were in one column.
>
> I put the dates for the year (Monday 12/31/2012 thru Sunday 1/5/2014 
> in  A5:A375.
> Then, in A1 I put whatever date I'm looking for.
> Off to the side (D1) I entered: =VLOOKUP($A$1,$A$5:$A$375,1,0)-(A5-1)+4
>      VLOOKUP(A1,$A$5:$A$375,1,1) finds the date you're looking for.
>      -(A5-1) subtracts the day before the first date on the list FROM 
> the date found.
>      +4 adds in the four rows above the range.
> The result is the row number of the date you're looking for.
A simpler formula: =MATCH($a$1,$a$5:$a$375,0)+row($a$5)-1

MATCH returns the offset into the array (so if $a$1 = $a$5, MATCH would 
return 1), and row($a$5)-1 adjusts it to give the absolute row.

The reason I do it this way is that if you name the array (e.g. name it 
YearRange), it works as follows:

=MATCH($a$1,YearRange,0)+row(YearRange)-1

So you don't need to know what row your array starts on to write the 
formula.

Jim

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted