xLookup if contains text

Copper Contributor

I tried this formula but it doesn't work 


I want to return specific value when containing specific text



5 Replies


What would you like the formula to return and why?

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





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


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))