I need help in excel formula lookup

[AGB200]

I need a lookup formula that get me the last cell that contain a specific part of text

 

 

 

[#2]

@AGB200 

=INDEX(A1:A8,LARGE(IF(ISNUMBER(SEARCH("S",A1:A8)),ROW(A1:A8)),1))

Maybe with this formula in cell C3. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

=INDEX(A1:A8,LARGE(IF(ISNUMBER(SEARCH("P",A1:A8)),ROW(A1:A8)),1))

Maybe with this formula in cell D3.

 

[#3]

@AGB200 As a variant AND only if you have Excel MS365 or 2021.

A2:A12 is a named range called myList.

Formula in D1:

=XLOOKUP({"p","s"},LEFT(myList),myList,,0,-1)