英文:
Is there a way to populate a dropdown category in Google Sheets using wildcard matches from another table?
问题
我正在尝试构建一个费用表格来分类费用。我有一个类似这样的表格：
| 类别 | 日期 | 描述 | 金额 |
|---|---|---|---|
| 1月1日 | 乐购购物 | $2.00 | |
| 1月2日 | 沃尔玛购物 | $3.43 |
我有另一个标签页，其中类别是从下拉值中获取的（列A）。
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 建筑 | 家得宝 | 乐购 | |||
| 杂货 | 沃尔玛 | 目标 | 克罗格 | 帕布里克斯 | 全食 |
| 餐厅 | 汉堡王 | 麦当劳 | 温迪 |
我正在尝试构建一个公式，如果在类别右侧的值中有任何匹配项，它将自动填充第一个表格中的类别值。例如，"乐购购物"行将自动更新为"杂货"，第二行将自动设置为"杂货"，因为有"沃尔玛"的通配符匹配。这可行吗？
我尝试过使用包含函数，但它只适用于一个值。不确定是否可以处理多个值，以及是否可以拥有整个值行的“最多”匹配。每一行可能有不同数量的可能匹配项。
英文:
I'm trying to build an expenses sheet to categorize expenses. I have a sheet like this:
| Category | Date | Description | Amount |
|---|---|---|---|
| Jan 1 | Lowes Purchase | $2.00 | |
| Jan 2 | Walmart Purchase | $3.43 |
I have another tab where Category is sourced from a dropdown of values (Column A).
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Construction | Home Depot | Lowes | |||
| Grocery | Walmart | Target | Krogers | Publix | Whole Foods |
| Restaurant | Burger King | McDonald's | Wendy's |
I am trying to build a formula that would auto-populate the Category value in the first table if there is ANY match of the values to the right of the Category. For example, the row Lowes Purchase would automatically update to Construction and the second row would automatically set to Grocery because of the Walmart wildcard match. Is this possible?
I've tried a contains but it only works with one value. Not sure if it's possible to do multiple values, and also have "up to" and entire row of values. Each row could have a different # of possible matches.
答案1
得分: 0
=FILTER($A$10:$A$12,BYROW($B$10:$F$12,LAMBDA(x,OR(INDEX(x=INDEX(SPLIT(C2," "),1,1))))))
要引用另一个工作表，请使用-
=FILTER(Sheet2!$A$1:$A$12,BYROW(Sheet2!$B$1:$F$12,LAMBDA(x,OR(INDEX(x=INDEX(SPLIT(C2," "),1,1))))))
英文:
You may try-
=FILTER($A$10:$A$12,BYROW($B$10:$F$12,LAMBDA(x,OR(INDEX(x=INDEX(SPLIT(C2," "),1,1))))))
To refer another sheet, use-
=FILTER(Sheet2!$A$1:$A$12,BYROW(Sheet2!$B$1:$F$12,LAMBDA(x,OR(INDEX(x=INDEX(SPLIT(C2," "),1,1))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论