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