I'm trying to write a formula that will calculate 5 earlies dates from the list I have. The list will grow - number of rows will be bigger, but number of columns will be max 12. Those dates are tied with some events, so they have to be in 2 dimensional array (table). What I wanted to do is to have 5 nearest dates listed, that will be equal or grater than today (past don't matter).
I've managed to do that with a function:
=SMALL(IF($C$1:$N$1000>=TODAY();$C$1:$N$1000);A1)
Where A1=1, B1=2, etc.
The problem is that I would also like to do other calculations and I must have the row number of the values it founds. I highligthed expected results.
I have no idea what function to write for that.
| Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H | Column I | Column J | Column K | Column L |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | COI0325 | 03.03.2022 | 03.03.2023 | 03.03.2024 | 03.03.2025 | ||||||
| 2 | COI0425 | 09.04.2022 | 09.04.2023 | 09.04.2024 | 09.04.2025 | ||||||
| 3 | COI0525 | 12.05.2022 | 12.05.2023 | 12.05.2024 | 12.05.2025 | ||||||
| 4 | COI0625 | 02.06.2022 | 02.06.2023 | 02.06.2024 | 02.06.2025 | ||||||
| 5 | COI0625 | 10.06.2022 | 10.06.2023 | 10.06.2024 | 10.06.2025 | ||||||
| 6 | COI0725 | 02.07.2022 | 02.07.2023 | 02.07.2024 | 02.07.2025 | ||||||
| 7 | COI0725 | 23.07.2022 | 23.07.2023 | 23.07.2024 | 23.07.2025 | ||||||
| 8 | COI0825 | 02.08.2022 | 02.08.2023 | 02.08.2024 | 02.08.2025 | ||||||
| 9 | COI0925 | 02.09.2022 | 02.09.2023 | 02.09.2024 | 02.09.2025 | ||||||
| 10 | COI1025 | 06.10.2022 | 06.10.2023 | 06.10.2024 | 06.10.2025 | ||||||
| 11 | COI1125 | 23.11.2022 | 23.11.2023 | 23.11.2024 | 23.11.2025 | ||||||
| 12 | COI1225 | 15.12.2022 | 15.12.2023 | 15.12.2024 | 15.12.2025 | ||||||
| 13 | COI0926 | 09.09.2023 | 09.09.2024 | 09.09.2025 | 09.09.2026 | ||||||
| 14 | COI1226 | 15.12.2023 | 15.12.2024 | 15.12.2025 | 15.12.2026 | ||||||
| 15 | COI0127 | 31.01.2024 | 31.01.2025 | 31.01.2026 | 31.01.2027 | ||||||
| 16 | COI0227 | 07.02.2024 | 07.02.2025 | 07.02.2026 | 07.02.2027 | ||||||
| 17 | COI0327 | 06.03.2024 | 06.03.2025 | 06.03.2026 | 06.03.2027 | 02.08.2023 | |||||
| 18 | COI0327 | 29.03.2024 | 29.03.2025 | 29.03.2026 | 29.03.2027 | ||||||
| 19 | COI0427 | 26.04.2024 | 26.04.2025 | 26.04.2026 | 26.04.2027 | ||||||
| 20 | COI0527 | 05.05.2024 | 05.05.2025 | 05.05.2026 | 05.05.2027 | ||||||
| 21 | COI0627 | 01.06.2024 | 01.06.2025 | 01.06.2026 | 01.06.2027 | ||||||
| 22 | COI0627 | 28.06.2024 | 28.06.2025 | 28.06.2026 | 28.06.2027 | ||||||
| 23 | COI0707 | 07.07.2024 | 07.07.2025 | 07.07.2026 | 07.07.2027 | ||||||
| 24 | EDO1032 | 01.10.2023 | 01.10.2024 | 01.10.2025 | 01.10.2026 | 01.10.2027 | 01.10.2028 | 01.10.2029 | 01.10.2030 | 01.10.2031 | 01.10.2032 |
| 25 | EDO1033 | 17.01.2024 | 17.01.2025 | 17.01.2026 | 17.01.2027 | 17.01.2028 | 17.01.2029 | 17.01.2030 | 17.01.2031 | 17.01.2032 | 17.01.2033 |
| 26 | COI0826 | 02.08.2023 | 02.08.2024 | 02.08.2025 | 02.08.2026 |
