• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Middle Column

Sophanith

New Member
Dear Team,

We have a question on have to get the data on how to get the data in the middle column in a range and the example is below. Excel format is in attached file
61502

Thanks

Phanith
 

Attachments

  • Middle Column.xlsx
    10.2 KB · Views: 12
Try in C3 (copy down)
Code:
=INDEX(F3:K3,MATCH(1E+99,F3:INDEX(F3:K3,MATCH(1E+99,F3:K3)-1)))
I hope it helped.
 

Attachments

  • sophanith-navic-42162.xlsx
    12.5 KB · Views: 5
A different strategy
Let the Name 'amount.date' refer to the dates associated with non-blank amounts
= IF( amount>0, date )
Calculate the second latest such date
= LARGE( amount.date, 2 )
and lookup the corresponding amount by date
= LOOKUP( LARGE(amount.date, 2), date, amount )
If the penultimate month is blank, the formula simply moves to the previous month for which there is an amount.
 

Attachments

  • Middle Column (PB).xlsx
    17 KB · Views: 8
Back
Top