Middle Column

[Sophanith]

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


Thanks

Phanith

 

[navic]

Try in C3 (copy down)

=INDEX(F3:K3,MATCH(1E+99,F3:INDEX(F3:K3,MATCH(1E+99,F3:K3)-1)))

I hope it helped.

 

[Sophanith]

Try in C3 (copy down)

=INDEX(F3:K3,MATCH(1E+99,F3:INDEX(F3:K3,MATCH(1E+99,F3:K3)-1)))

I hope it helped.

Thanks for your help it work

Thanks

Phanith

 

[navic]

You are welcome

 

[bosco_yip]

Or….................

In C3, copied down :

=LOOKUP(9^9,F3:INDEX(3:3,MATCH(9^9,3:3)-1))

Regards
Bosco

 

[Sophanith]

Or….................

In C3, copied down :

=LOOKUP(9^9,F3:INDEX(3:3,MATCH(9^9,3:3)-1))

Regards
Bosco

Thank you bosco. This really help

Phanith

 

[Peter Bartholomew]

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.