xLookup if contains text

[Mohamed_Naguib]

I tried this formula but it doesn't work 

I want to return specific value when containing specific text

[#2]

@Mohamed_Naguib 

What would you like the formula to return and why?

[#3]

To return the Crown word and I am using this formula because when sentence contains any of these words, I want to return the crown word

[#4]

@Mohamed_Naguib 

Perhaps

=INDEX(G5:G13,MATCH(TRUE,ISNUMBER(SEARCH(F5:F13,J5)),0))

[#5]

Still not working as I want. I need to fix the issue in my formula in the above attached excel

[#6]

@Mohamed_Naguib 

The XLOOKUP as constructed won't work because it's checking a larger string against smaller strings.

Depending on what you're looking to do (and what the rest of data looks like) and if you're trying to return the last instance of The Crown, this might be a solution for you:

=LET(result,XLOOKUP(TEXTSPLIT(J5," - "),$F$5:$F$13,$G$5:$G$13),TOCOL(result,3))