Finding last text in a filtered column

[Glasgow Girl]

Hi I am using someone else's spreadsheet and trying to adapt it. I need the last text in a column to show in another cell.

I have a formula to find the last bit of text in a column, which works, but not if I filter the column: =INDEX(J:J,MATCH("*",J:J,-1))

Is this possible?

Thanks

 

[hagia_sofia]

Hello, could you possibly answer what version of Excel are using?

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Domenic]

Here's one way...

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(J2:J100,ROW(J2:J100)-ROW(J2),0,1)),J2:J100)

Hope this helps!

 

[Glasgow Girl]

Here's one way...

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(J2:J100,ROW(J2:J100)-ROW(J2),0,1)),J2:J100)

Hope this helps!

I will give it a go, thank you

 

[Domenic]

That's great, but don't forget to update your profile to reflect your version of Excel. Depending on your version of Excel, there may be a more efficient formula, as @Joe4 and @hagia_sofia have suggested.

 

[Glasgow Girl]

Here's one way...

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(J2:J100,ROW(J2:J100)-ROW(J2),0,1)),J2:J100)

Hope this helps!

Sorry for delay, was on lunch. Tried this formula and it works! Thank you so much for your help, it's much appreciated

 

[Joe4]

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.