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
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
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
Try this:
=INDEX(B5:AJ5,AGGREGATE(14,6,COLUMN(B5:AJ5)/(B5:AJ5<>""),2)-1)
Hi,
I have attached a document that demonstrates what I am looking for. Hope this helps.
E5 cell , Array formula,Drag down
HTML Code:
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
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,
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
this works great. Many thanks as always
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks