Formula to return only the last "Submitted" in a row of data.

[bigbear22860]

Hi Everyone,

I hope you can help. I have looked all through the website looking for a solution and have not located one as of yet.
1st formula:
I have a large file with several colums that contain the word "Submitted"
I am looking for a formula that will locate the last Text "Submitted" in that row and return the cell data into cell A2.
2nd Formula:
And to make it even more complicated- I am trying to obtain the data from the cell directly to the left of the last cell titled "Submitted" in the row.

I have attached an excell sheet with an example. (I just manually copied and pasted the info into cell A2 and B2 for a visual example)

Thanks, MikeExcel Help.xlsx

[bigbear22860]

Please let me know if I need to explain anymore in detail?

I have tried several formula's. I have found a formula that will find the word "submitted" in the row- but since each row has duplicate "Submitted" titles, I must be able to find the furthest right. This would be the last transaction.

[Alex H]

Hi Mike,

Welcome to the forum.

Assuming as you say that the furthest right "Submitted" comment is the last one you could use a helper worksheet, see Sheet2 in attached workbook, along with the index function to look for and return the furthest right "Submitted".

I'm sure there's a way to do it on the same sheet using array formulae but I can't think how. Perhaps one of the more experienced users on here can help you with that if you're interested.

Excel Help AH.xlsx

Best regards,
Alex

[bigbear22860]

Hi Alex,

Thanks for your help. It looks like it returned the proper information. I am just a little unsure what all the info is on the second sheet? And how do I add that info into a larger file I need to search?

Thanks,

Mike

[tigeravatar]

bigbear22860,

Welcome to the forum!
Attached is a modified version of your example workbook.
In cell B2 and copied down is this formula:

Please Login or Register  to view this content.

In cell C2 and copied down is this formula:

Please Login or Register  to view this content.

Does that work for you?

[bigbear22860]

Thank you for your help. You are like an Excel GOD. The formula works great. Thanks- Mike