Help with nested isnumber(search) in IF and multiple hits

[maximillianrg]

Hello Excel Masters

• I have a 9,000 row excel sheet that contains an export of a directory structure
• The below formula searches F2 for 1 of 8 key words and if it finds it, it returns the value of that keyword in cell B2 and if it does not find the value it returns False
• The Keywords are: response, resp, narrative, request, production, letter, exhibits, annex, rfi,

=if(ISNUMBER(SEARCH("response",F2)),"response",if(ISNUMBER(SEARCH("resp",F2)),"resp",if(ISNUMBER(SEARCH("narrative",F2)),"narrative",if(ISNUMBER(SEARCH("request",F2)),"request",if(ISNUMBER(SEARCH("production",F2)),"production",if(ISNUMBER(SEARCH("letter",F2)),"letter",if(ISNUMBER(SEARCH("exhibits",F2)),"exhibits",if(ISNUMBER(SEARCH("annex",F2)),"annex",if(ISNUMBER(SEARCH("rfi",F2)),"rfi",if(ISNUMBER(SEARCH("RFI",F2)),"Other"))))))))))


This works great if cell F2 contains 0 or 1 of the keywords but if multiple keywords exist in cell 2 then I need to find a way to return the last keyword in the cell the formula finds.

Example If F2 = C:\Regulatory\Asia\Athens\Response\Submitted\Exhibits\Annex\Approved
The formula would need to return "annex" and not Exhibits or Response

 

[DRSteele]

Let's try this.

MrExcel posts19.xlsx
	B	C	D	E	F
1
2					C:\Regulatory\Asia\Athens\Response\Submitted\Exhibits\Annex\Approved
3
4			Excel365	ye Olde Excel
5			annex	annex
6
7	keywords
8	response
9	resp
10	narrative
11	request
12	production
13	letter
14	exhibits
15	annex
16	rfi
Sheet24

Cell Formulas
Range	Formula
D5	D5	=XLOOKUP(LEN(F2),IFERROR(SEARCH(B8:B16,F2),0),B8:B16,,-1)
E5	E5	=LOOKUP(MAX(IFERROR(SEARCH(B8:B16,F2),0)),IFERROR(SEARCH(B8:B16,F2),0),B8:B16)

 

[maximillianrg]

Thank you so much DrSteel for the above answers. It worked perfectly and I ended up using the formula in cell E5

 

[DRSteele]

You're welcome. Glad to have helped.