I’m working with a column that has multiple cells containing various text values and some empty ones. I need to find the first cell that contains a specific word or phrase as a partial match, not an exact match. I already know how to use Index and Match together for exact text searches, but that won’t work for what I need. I tried using the Search function but it seems like it only works on single cells, not ranges. Is there a way to scan through multiple cells and find the first one that contains part of the text I’m looking for? I want to stick with formulas only and avoid using Google Apps Script if possible.
xlookup with wildcards works gr8 if u’ve got newer sheets - =XLOOKUP("*your_text*",A:A,A:A,,2). That 2 at the end enables wildcard matching. way cleaner than regex and handles partial matches perfectly. I’ve been using this since they rolled out XLOOKUP.
Use INDEX with SEARCH in an array formula. The key is wrapping SEARCH() with ISNUMBER() to get TRUE/FALSE values that MATCH can handle. Here’s the formula: =INDEX(A:A,MATCH(TRUE,ISNUMBER(SEARCH("your_text",A:A)),0)). Just swap “your_text” for whatever you’re looking for and A:A for your column range. SEARCH returns a number when it finds the text (or an error when it doesn’t), ISNUMBER turns that into TRUE/FALSE, and MATCH grabs the first TRUE. Older Sheets versions need this entered as an array formula, but newer ones do it automatically. I use this all the time with messy data imports where exact matches won’t work.
Try REGEXMATCH with INDEX and MATCH instead: =INDEX(A:A,MATCH(TRUE,REGEXMATCH(A:A,"your_partial_text"),0)). Way more reliable than SEARCH since REGEXMATCH handles text patterns better and you don’t need the ISNUMBER wrapper. Add (?i) before your text to make it case-insensitive. I’ve used this for two years with product catalogs where descriptions change but have the same key identifiers. Regex also lets you search for multiple variations of the same term later.