FIND Function; looking for an array of strings and returning found string value

Hello, good day.

I'm dealing with a simple table of names (Last Name, First Name, Middle Name, Suffix). The Suffix column is a newly requested addition for migration into an MS SQL database. Currently, all the suffixes (Jr, Sr) are placed in the First Name column (i.e. John Jr.).

I'm looking for a simple formula that will (a) find either "Jr." or "Sr." in the indicated cell and then (b) return that found string. Currently, this is what I have:

=IF(ISERROR(FIND({"Jr.","Sr."},H3)),"",RIGHT(H3,LEN(H3)-FIND(" ",H3)))

It only returns the "Jr." values. I know something's wrong. Can you help me with this? Without using another Index formula referencing two cells containing just Jr. and Sr. to combine with Match, I mean.

I would very much appreciate it. Thank you!

Thank you so much! Worked like a charm.

But is there a way to do this without having Jr. and Sr. as reference strings placed outside the table? Or does this method take up less memory in the Excel file?

It worked! Thank you very much! May the force be with you

Done thank you again! Hope to be of help to someone else in the forum in the near future!

By the way -- sorry if I may seem a bit naggy -- I tried understanding the formula itself, and how it works, tracing the step by step events that occur. For self-educating purposes and future knowledge. I'm a bit lost. How does the OR function fit into the formula? It seems so integral. Does it trigger the loop into the array? It's absence (and presence) seem to be the key determinant. How?

Oh, I understand now. The combination of ISNUMBER and SEARCH functions work as one, in a distributive pattern (kind of like "x(x+y)"), and the results are the ones being used as OR arguments. That's what what was bugging me, how the OR was so significant.

Thank you so much again! I appreciate the help and the additional learnings!