Forum Discussion
xLookup if contains text
I tried this formula but it doesn't work
I want to return specific value when containing specific text
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
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))
