CODEWITHSUNDEEP
libreofficelibreoffice-calc
Bazman
Bazman

Reputation: 2150

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?

Upvotes: 1

Views: 159

Answers (1)

Jim K
Jim K

Reputation: 13819

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;"")

Upvotes: 1

Related Questions