Search code examples
libreofficelibreoffice-calc

VLOOKUP between two dates with multiple return values in libra calc

I want to be able to look up values between two dates and return values between the two dates.
It's a like a Vlookup but the target it a range and it needs to return multiple values.

enter image description here

The image above shows the logic all values with dates after 01/03/16 and less than 26/06/17 are in the first group, all dates more than or equal to 26/06/17 are in the second group. Can this be done in Librecalc?

Solution

  • Enter the following as an array formula to produce 230\n250 in a single cell:

    =TEXTJOIN(CHAR(10);TRUE();IF((C2:C6>=A$2)*(C2:C6<=A$3); D2:D6; ""))

    The advantage of TEXTJOIN is that non-matching rows will not be seen as extra results — they'll be concatenated as "".

    If you need only one value in each cell, then enter the following and fill down, then go to Data > More Filters > Standard Filter to hide empty rows:

    =IF(AND(C2>=A$2;C2<=A$3);D2;"")