I have the following array on Google Sheets :
| Task name | Date |
|---|---|
| Task 1 | 10/08/2022 |
| Task 2 | 10/08/2022 |
| Task 3 | 05/08/2022 |
| Task 4 | 10/10/2022 |
| Task 5 | 11/11/2022 |
| Task 6 | 20/11/2022 |
| Task 7 | 10/12/2022 |
| Task 8 | 20/12/2022 |
| Task 9 | 02/07/2022 |
| Task 10 | 01/06/2022 |
I am trying to get the 5 closest past dates from the current day TODAY() in colum B. Then, I need to return the corresponding task names. Some dates are identical.
In this case, it would return : Task 1, Task 2, Task 3, Task 9, Task 10.
I've tried using a combination of different formulas with no success.
CodePudding user response：
What about QUERY() function like-
=QUERY(A1:B10,"select * order by B limit 5")
Or FILTER() like-
=FILTER(A1:A10,B1:B10<TODAY())
To show all result in a single cell use JOIN() function.
=JOIN(", ",FILTER(A1:A10,B1:B10<TODAY()))
Edit: Best would be using single
QUERY()function.
=QUERY(A1:B,"select A where B <= date '" & TEXT(TODAY(),"yyyy-mm-dd") & "' order by B DESC limit 5")
