Search code examples
google-sheetsformula

Google Sheets: by the last non-empty cell in column, return the value in corresponding cell in another column?

 Hello!
 So I have dates in Column A (A3:A). And I have transactions (formatted as currency) in Column C (C3:C). So I want a cell D3 in Column D to show the date (preferably only month but it is not vital) of the last transaction.
 In other words I need a formula that would check the whole Column C, find the last transaction (assuming that there will be blank cells in that column), find the corresponding date in Column A (date when transaction occured) and return that value (date) in cell D3 of Column D.
 INDEX? MATCH? HLOOKUP?
 Please advise.

Column A          Column C          Column D

3 01/01/2021            10.98                       01/05/2021
4 01/02/2021            15.64
5 01/03/2021
6 01/04/2021
7 01/05/2021            20.93
8 01/05/2021            76.28

Solution

  • Try

    =+sort(A:A, row(A:A)*(C:C<>""),)

    or

    =INDEX(A:A, MATCH(2, IF(C:C<>"", 1), 1))

    or even

    =lookup(9^9, C:C, A:A)

    and see if that works?