where keywords is the named range E5:E14, and categories is the named range F5:F14. Note: this is an array formula and must be entered with control + shift + enter. Inside the MATCH function, we use the SEARCH function to search cells in column B for every listed keyword in the named range keywords (E5:E14): Because we are looking for multiple items (in the named range keywords), we’ll get back multiple results like this: The #VALUE! error occurs when SEARCH can’t find the text. When SEARCH does find a match, it returns a number that corresponds to the position of the text inside the cell. To change these results into a more usable format, we use the ISNUMBER function, which converts all values to TRUE/FALSE like so: This array goes into the MATCH function as the lookup_array, with the lookup_value set as TRUE. MATCH returns the position of the first TRUE it finds in the array (7 in this case) which is provided to the INDEX function as the row_num: INDEX returns the 7th item in categories, “Auto”, as a final result.
With XLOOKUP
With the XLOOKUP function, this formula can be simplified somewhat. XLOOKUP can utilize the same logic used inside the MATCH function above, so the equivalent formula is: XLOOKUP locates the first TRUE in the array, and returns the corresponding value from categories.
Preventing false matches
One problem with this approach is you may get false matches from substrings that appear inside longer words. For example, if you try to match “dr” you may also find “Andrea”, “drink”, “dry”, etc. since “dr” appears inside these words. This happens because SEARCH automatically does a “contains” match. For a quick hack, you can add space around the search words (i.e. " dr “, or “dr “) to avoid catching “dr” in another word. But this will fail if “dr” appears first or last in a cell, or appears with punctuation, etc. If you need a more accurate solution, one option is to normalize the text first in a helper column, taking care to also add a leading and trailing space. Then you can search for whole words surrounded by spaces.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
