Mar 09 2023 03:56 AM - edited Mar 09 2023 03:58 AM
I tried this formula but it doesn't work
I want to return specific value when containing specific text
Mar 09 2023 04:21 AM
What would you like the formula to return and why?
Mar 09 2023 04:32 AM
Mar 09 2023 04:59 AM
Mar 09 2023 05:43 AM
Mar 09 2023 06:08 AM - edited Mar 09 2023 06:12 AM
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))