Hi - Dave here.
Happy Friday!
A tricky problem in Excel is how to categorize text records based on specific keywords, as seen in the worksheet below.
The challenge is that we aren't looking for
one keyword, we're looking for
many keywords. In addition, when we do find a keyword, we need a way to return the correct category for that keyword.
One way to solve this problem is with the XLOOKUP function and a table that lists keywords and categories. This is the approach seen below where the formula in cell C5, copied down, looks like this:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keyword,B5)),category)
As the formula is copied down, it searches the text in column B for a matching keyword in E5:E13 and returns the associated category from F5:F13.
[
Download the workbook and read the full explanation]
The gist of this formula is that we build a special lookup array using SEARCH + ISNUMBER that indicates "found" keywords with TRUE and FALSE values. Then we use XLOOKUP to locate the first TRUE value in the array and return the associated category for that keyword. The keywords and categories are completely arbitrary and can be changed to suit your needs. Download the workbook and try it out yourself!
Note: XLOOKUP is only available in Excel 2021 or later. In older versions of Excel without XLOOKUP you can use the same idea in a formula based on INDEX and MATCH. The link above includes both formulas.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
