Excel: How to get value of a row thats below the current cell that has value?

user_16490122 0
Viewed 48

My excel data looks like this

Date          value        Formula
31/01/2021                  5/2/2021      
2/2/2021                    5/2/2021
5/2/2021       100          5/2/2021
8/2/2021                    15/2/2021
11/2/2021                   15/2/2021
15/2/2021       50          15/2/2021

As you can see the formula column has dates where the next row has value in it. on 2/2/2021 the next row with value is on 5/2/2021 but on 8/2/2021 the next row with value is on 15/2/2021 so that it has date 15/2/2021 in it.

How can I achieve this using formula? I need to get the date value.

excel
1 Answers

With Excel-365 you can try-

=INDEX(FILTER($A$2:$A$7,$B$2:$B$7<>""),COUNTA($B$1:B1))

For older version use AGGREGATE() function. Try-

=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($1:$6)/($B$2:$B$7<>""),COUNTA($B$1:B1))),"")

enter image description here

user_55147470
Related