Search code examples
excelexcel-formulaarray-formulas

Check if cell contains strings in a list

I have an Excel sheet with a lot of different entries (expenses) that I want to classify. To do that, I created another sheet with different entry categories such as groceries, night out etc., and I put each in a column.

So this is my expenses sheet:

Description Value Category
Card - Foetex purchase on 3/10 350 ?
Card - Mikkeller purchase on 3/10 350 ?

And this would be the categories sheet:

Groceries Night out
Foetex Mikkeler
Irma To Oel

What I want to do is to have the Category defined on the categories sheet (Groceries and Night Out) on the 3rd column of the Expenses sheet. So what is in the categories sheet would be a substring of the text on the description column of the expenses sheet. So if, for example, the description has "Foetex" in it, then the Category should be Groceries.

I have tried Match, countif etc., but to no avail.

Does anyone know a way out of this?

Solution

  • For this answer I have made the following assumptions:

    • Sheets are named "Expenses" and "Categories", and are in the same workbook
    • Both the above tables start at Cell A1

    The formula in Sheet "Expenses" Cell C2 (your 'Category' column) should be:

    =CONCATENATE(IF(COUNTIF(A2, "*" & Categories!A$2:B$3 & "*")>0, Categories!A$1:B$1, "")) then press ctrl+enter to apply it as an array formula.

    Replace Categories!A$1:B$3 With your categories range if it is longer or wider, make sure that second range Categories!A$1:B$1 stays stuck to the top row though.

    And Remember to use absolute references to save the range changing when you copy the formula down the rest of the category column.