Google Spreadsheet:带有通配符的vlookup不在“search_key”中但是在“范围” [英] Google Spreadsheet: vlookup with wildcards not in "search_key" but in "range"
问题描述
问题:
在这里阅读几篇文章,我真的很接近找到解决方案,但现在我无法解决一个问题。我收到了一列文字,应该用其他文字替换。基本上,单元格中的abc(在任何地方,因此可以是123abc456),它应该被转换为ABC(而不是123ABC3456)。并且bcd - > BCD在哪里。并为此我得到了替换的特殊表格(这就是为什么我不能使用IFS的原因)
我试图使用vlookup
通配符不起作用,google向D1添加通配符,而不是a2:a5(在search_key中但不是range)所以基本上我在寻找反向查找,我可以在范围单元格中使用通配符,而不是search_key。
谢谢!
确定有更复杂的方法来实现你的目标,但这是有效的......
VLOOKUP(FILTER(A2: A5,REGEXMATCH(D1,A2:A5)),A2:B5,2,FALSE)
< a href =https://docs.google.com/spreadsheets/d/19ReuxxOkNorQ4LME__iTqWnOqbbbEh-8RMt9X_B5drA/edit?usp=sharing =nofollow noreferrer> Google表格
Just read couple of articles here, I am really close to find the solution, but now I can not solve one issue.
The problem: I got one column with some texts which should be replaced by other text. Basically, where in a cell is "abc" (everywhere, so it can be "123abc456") it should be converted to ABC (not to 123ABC3456). And where is "bcd" -> BCD. and for this I got special table with replacements (this is the reason why I can not use IFS)
I tried to use vlookup
Wildcards are not working, google is adding wildcards to D1, not a2:a5 (in "search_key" but not to "range") So basically I am looking for "reversed vlookup" where I can use wildcards with range cells, not search_key.
thank you!
I'm sure there are more sophisticated ways to achieve what you're after, but this works...
VLOOKUP(FILTER(A2:A5,REGEXMATCH(D1,A2:A5)),A2:B5,2,FALSE)
这篇关于Google Spreadsheet:带有通配符的vlookup不在“search_key”中但是在“范围”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!