如何在Excel中通过部分匹配为CSV银行流水设置支出分类

Hey Larry, this is such a common (and time-saving!) task for sorting bank CSV data. Let’s walk through a few straightforward methods to get your Category column populated based on partial matches in the Description field.

Method 1: Nested IF + SEARCH (Great for Small Keyword Lists) #

If you only have a handful of categories to match, a nested IF formula works perfectly. It checks for each keyword in order and assigns the corresponding category.

Assuming:

• Your Description column is column B
• Your Category column is column D (empty, ready to populate)

Drop this formula into cell D2 and drag it down the column:

=IF(ISNUMBER(SEARCH("SPROUTS", B2)), "FOOD",
 IF(ISNUMBER(SEARCH("ARCO", B2)), "AUTO EXPENSE",
 "UNCATEGORIZED"))

Breakdown: #

• SEARCH("SPROUTS", B2) looks for the text "SPROUTS" anywhere in cell B2 (it’s case-insensitive, so it’ll catch "sprouts" or "Sprouts" too)
• ISNUMBER(...) converts the search result (a position number if found, #VALUE! if not) into a TRUE/FALSE value
• The nested IFs check each keyword in order—if none match, it defaults to "UNCATEGORIZED" (you can change this to a blank "" if preferred)

Method 2: SWITCH + SEARCH (Cleaner for Medium-Sized Keyword Lists) #

If you have more than 3-4 categories, nested IFs get messy. The SWITCH function makes this cleaner and easier to read.

Same column assumptions as above, use this formula in D2:

=SWITCH(TRUE,
 ISNUMBER(SEARCH("SPROUTS", B2)), "FOOD",
 ISNUMBER(SEARCH("ARCO", B2)), "AUTO EXPENSE",
 ISNUMBER(SEARCH("WALMART", B2)), "GENERAL MERCH",
 "UNCATEGORIZED")

Why this works: #

• SWITCH(TRUE, ...) lets you list multiple condition-result pairs in a neat list
• Add as many lines as you need for additional keywords (e.g., "WALMART" → "GENERAL MERCH")
• Still case-insensitive and handles partial matches perfectly

Method 3: Helper Table + XLOOKUP (Best for Large Keyword Lists) #

If you have dozens of keywords/categories, a helper table is the way to go—it’s easy to update and maintain without editing formulas every time.

Step 1: Create a Helper Table #

Add a new sheet (name it "Categories") and set up two columns:

Step 2: Use XLOOKUP with SEARCH #

Back in your main transaction sheet, use this formula in D2:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Categories!$A$2:$A$100, B2)), Categories!$B$2:$B$100, "UNCATEGORIZED")

Notes: #

• Adjust Categories!$A$2:$A$100 and Categories!$B$2:$B$100 to match the size of your helper table
• This will find the first matching keyword in your helper table (so order matters if a transaction could match multiple keywords)
• To make it dynamic (so the table expands automatically when you add new keywords), convert your helper table to an Excel Table (select the range → Ctrl+T) and use structured references:

  =XLOOKUP(TRUE, ISNUMBER(SEARCH(Categories[Keyword], B2)), Categories[Category], "UNCATEGORIZED")
  

Pro Tips: #

• If you need case-sensitive matches, replace SEARCH with FIND
• To handle transactions that might match multiple keywords, arrange your conditions in priority order (e.g., if a transaction has both "SPROUTS" and "ARCO", the first condition in the formula/table will be used)
• After applying the formula, you can copy the Category column and paste values to lock in the categories (so they don’t change if you edit the Description column later)

内容的提问来源于stack exchange，提问作者Larry Levenson