Error trying to lookup date range

[sonogenics]

I have imported a spreadsheet create in Microsoft Excel 2010 into Calc and most things work fine apart from 1 fairly critical thing. I have 1 sheets with a selection of date ranges identified, and another sheet with a single date and I am trying to determine what period this single date lies in.
This works perfectly in excel and I am able to get the expected output, something like:

Start Date | Week

2015-02-01 | 6

2015-02-14 | 8

The formula in the Week cell is:

=LOOKUP(2,1/((B2>=$'5 - Week Periods'.$B$2:$B$54)*(B2<=$'5 - Week Periods'.$C$2:$C$54)),$'5 - Week Periods'.$A$2:$A$54)

And the 5 - Week Periods sheet looks something like this:

Week | Start | End

1 | 12/27/2014 | 1/2/2015

2 | 1/3/2015 | 1/9/2015

3 | 1/10/2015 | 1/16/2015

…

However, when I try to run this in Libreoffice Calc (4.2.7.2), the calculcated week numbers work fine until I change the lookup date and then I get
#DIV/0!

It would appear that (B2>=$'5 - Week Periods'.$B$2:$B$54)*(B2<=$'5 - Week Periods'.$C$2:$C$54) is returning 0.
Any ideas on how I can fix this or is there an alternate way I can get this to work?

[Vitaly]

It’s a little bit difficult to quickly reproduce your data structure to find out why the formula doesn’t work. But if the weeks at “5 - Week Periods” sheet are listed one-by-one without skipping days then, IMHO, your way of calculation is too complicated.


Consider this:

=WEEKS(A1;First;0)+1

Here, A1 is your “StartDate” cell, and “First” is name of cell that contains date of beginning of the first week (12/27/2014 in your sample). The third parameter may be set to 0 or 1 depending on what exactly you want to calculate - see help file for details regarding WEEKS function.

[karolus]

Hallo

@Vitaly +1 for your Formula,

using the '5 - Week Periods'.$B$2:$B$54 reference:

=MATCH( B2; $'5 - Week Periods'.$B$2:$B$54)

also does the job

[mariosv]

A question with a similar lookup issue in:
How do I lookup and return a most recent value?

[karolus]

and -1 for your link — this is not a lets made complicate-contest

[mariosv]

Please @karolus forgive me the life, my intellect is far from yours. With your permission I was thinking about show, how to solve this particular case of lookup in calc. But if a master says it’s not fine, sure it’s not fine.

[karolus]

Don’t bullshit – I’m pretty sure youre able to follow the two simple solutions here - no needs to provide an overhelmed one!