google-sheets - 反向通配符 Vlookup
问题描述
我正在尝试对我执行看似反向通配符查找的操作,但如果有人对更好的公式有建议,我完全愿意接受。
我有一列在单元格 ex:(房子是棕色的) 中有多个单词,然后我有一个表格,其中有一列颜色和一列值 ex:(A2=brown, B2=1738) 和什么我想做的是关闭 vlookup(房子是棕色的)并返回 1738 值。
在我看来,公式看起来像这样
=vlookup("the house was brown",sheet2!"*"&A:B&"*",2,false)
通配符附加到查找值的位置,但这似乎不起作用,我已经玩弄了通配符的位置,但一无所获。
任何帮助将非常感激。
谢谢!
解决方案
从问题中,您有如下数据:
A1:A4 中
的数据 老鼠是棕色
的 房子是棕色
的 车是红色
的 知更鸟是红色的
由表格的查找表补充
命名范围 "LookupTable"
Col1 ; Col2
棕色;5
红色;6
蓝色;7
等
那么公式,
=trim(join(" ",arrayformula(iferror(vlookup((iferror(regexextract(lower(A1),"\b"&LookupTableCol1&"\b"),"")),LookupTable,2,FALSE),""))))
在 B1:B4 中,其中 A1 是 LookupTable 的右上角,给出输出
输出:
5
5 6
6
6
也就是说,查找表中单词的每个实例都由关联的数字标识,并且这些数字在单个单元格中一次列出(空格分隔)一个。
请注意,查找表必须小写才能获得正匹配。
那是你所追求的吗?
为了编辑、测试或完善答案,这里有一个电子表格的链接,显示了这一点,一旦答案被接受,我将删除它。
澄清这是如何
工作的
(1) 通过使用带有字符串文字的正则表达式来识别 LookupTableCol1 中查找词之一的存在。在每一端添加的“\b”部分确保它作为一个完整的单词匹配,并且使用lower()函数来确保我们的结果不区分大小写。
(2) 然后将其输入到 vlookup() 以在查找表中搜索该单词并返回相关值。
(3) 如果在 regexextract() 或 vlookup() 中未找到匹配项,则使用“iferror()”删除任何“N/A”错误。
(4) Arrayformula() 用于沿着 LookupTableCol1 重复这些操作并返回一个数组 (5) Join() 用于将整个数组连接成一个单元格以提高可读性。
(6) Trim() 用于删除由 arrayformula 返回的数组中的空白单元格产生的任何前导或尾随空格,这是对包含空白单元格的数组使用 join() 的结果。
推荐阅读
- css - 如何使用 css 将电话输入分成不同的盒子(每个盒子一个数字)
- javascript - 如何在JS中破坏数组中的最后一项?
- javascript - 如何将js中的一堆对象映射到另一个对象
- javascript - Vue.js refs 是未定义的,即使 this.$refs 显示它们在那里
- python - 计算日期出现在熊猫时间序列列表中的次数
- ruby-on-rails - 在 Rails 中创建对象时如何从 Postgres 计算函数中获取值
- python - 在 python 3.X 中从用户输入创建一个 json 文件
- python - 仅当使用 Django-CMS 重新启动服务器时才更新用户页面权限
- install4j - install4j InvalidByteCodeException
- mysql - 在 SQL 中的链接服务器上使用 OPENQUERY 进行“INSERT IGNORE INTO”时出错