Google Sheets个人支出仪表盘：实现按商品名自动匹配类别列名

Got it, let's fix this so your expense dashboard automatically pulls the category header instead of just the matching keyword, and works across multiple columns in your Categories sheet.

First, Let's Confirm the Setup #

I’m assuming your Categories sheet is structured like this:

• Row 1: Contains your category names (these are the values we want to return, e.g., Groceries, Transport, Dining)
• Rows 2 onwards: Each column has regex patterns or keywords for items that belong to that category (e.g., .*coffee.* in the Dining column, train|bus in Transport)

The Formula to Use #

In your Expenses Feb 18 sheet, go to the first empty cell in your "Type" column (let's say this is cell B2, and your item names are in column A), paste this formula:

=ARRAYFORMULA(IF(A2:A="", "", 
  BYROW(A2:A, LAMBDA(item, 
    JOIN(", ", 
      FILTER(Categories!1:1, 
        MMULT(--REGEXMATCH(item, Categories!2:100), SEQUENCE(ROWS(Categories!2:100), 1, 1, 0)) > 0
      )
    )
  ))
))

How This Works (Breakdown) #

Let's break down the parts so you can tweak it to your exact setup:

• ARRAYFORMULA: Makes the formula apply to the entire column automatically—no need to drag it down.
• IF(A2:A="", "", ...): Skips empty item cells to avoid showing errors or blank results where there's no data.
• BYROW(A2:A, LAMBDA(item, ...)): Processes each item name in column A one row at a time.
• FILTER(Categories!1:1, ...): Grabs the category headers (row 1 of Categories) where the item matches any keyword in that column.
• MMULT(--REGEXMATCH(item, Categories!2:100), SEQUENCE(...)) > 0: Checks if the item matches any regex/keyword in a category column. The -- converts boolean matches to 1s and 0s, and MMULT sums them up—if the sum is greater than 0, there's a match.
• JOIN(", ", ...): If an item matches multiple categories (e.g., a snack bought at a train station), it joins those category names with a comma. If you only want the first matching category, replace this with INDEX(..., 1).

Tweaks for Your Exact Sheet #

• Adjust keyword range: If your Categories sheet doesn't have keywords up to row 100, change Categories!2:100 to your actual range (e.g., Categories!2:50).
• Change item column: If your item names are in column C instead of A, update A2:A to C2:C.
• No multiple matches: To only return the first matching category, modify the formula inside BYROW to:

  INDEX(FILTER(Categories!1:1, MMULT(--REGEXMATCH(item, Categories!2:100), SEQUENCE(ROWS(Categories!2:100),1,1,0))>0), 1)
  

Example Scenario #

If your Categories sheet looks like this:

• For an item named "Whole milk carton", the formula returns Groceries
• For an item named "Bus ticket & coffee", it returns Transport, Dining

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