Second to last non-empty cell

[sirdon]

Hi all,

I am trying to get an output in cell A5 where i am looking to find the second-to-last non-empty cell in a row range from B5:AJ5

Many thanks

[wk9128]

Good morning welcome to the forum

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen

[Phuocam]

Try this:

=INDEX(B5:AJ5,AGGREGATE(14,6,COLUMN(B5:AJ5)/(B5:AJ5<>""),2)-1)

[sirdon]

Hi,

I have attached a document that demonstrates what I am looking for. Hope this helps.

[wk9128]

E5 cell , Array formula,Drag down

[Bo_Ry]

Please try

=INDEX($5:$5,LARGE(INDEX(COLUMN($J$5:$Z$5)*($J$5:$Z$5>0),),ROWS(F$5:F5)))

or not use whole Row
=INDEX($J$5:$Z$5,LARGE(INDEX(COLUMN($J$5:$Z$5)*($J$5:$Z$5>0),),ROWS(F$5:F5))-COLUMN($J$5)+1)

and use =LOOKUP("Ω",J5:Z5) for last non blank text

[sirdon]

Thanks guys for your response. Sorry I should have set the layout better. I want the results to go across, not down. I have attached an updated version.

Regards,

[Bo_Ry]

H5
=INDEX($J$5:$Z$5,LARGE(INDEX(COLUMN($J$5:$Z$5)*($J$5:$Z$5>0),),COLUMNS($H5:H5))-COLUMN($J$5)+1)

copy to E5

[sirdon]

this works great. Many thanks as always