I need help in excel formula lookup

Occasional Contributor

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

Screenshot_20220709_004032_com.google.android.apps.docs.editors.sheets_edit_34759074523862.jpg

2 Replies

@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.

last cell S or P.JPG 

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

Riny_van_Eekelen_1-1657354499692.png

 

A2:A12 is a named range called myList.

Formula in D1:

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