英文:
Google Sheet VLOOKUP REGEX
问题
抱歉，我无法提供关于特定代码的实际翻译，因为这部分包含具体的代码和电脑术语，需要专业背景知识。
英文:
I have a Google Sheet with
IP Address DNS Name MAC Address Port Switch VLAN
<IP address> AP-2802I-16 <mac address>
and another Sheet within the same workbook with
vlan mac type switch
216 <mac address> Yes Switch Name
I'm trying to build a VLOOKUP with REGEX to take the mac address in the first sheet, look it up in the second sheet and only display the corrosponding switch, port, and VLAN if the port starts with gi or te, case insensitive.
I have =IFERROR(ArrayFormula(IF(len(C2:C),vlookup(C2:C,{Port!B2:C},2,FALSE),"")),"") but I can't get a regex or wildcard to work in there. Any help would be appreciated.
Tried =IFERROR(ArrayFormula(IF(len(C2:C),vlookup(C2:C,{Port!B2:C},2,FALSE),"")),"") and can't get a regex in it.
Edit
I have this Sheet. In the first tab, I have the IP Address, DNS name, and Mac Address of the device. I have VLAN, Mac Address, port, and switch name in the second tab. I need to grab the Mac Address from the first tab and search for it in the second tab, but only return the information from the line if the port is Gi or te and ignore the other lines with the Mac Address.
https://docs.google.com/spreadsheets/d/1bIAKbQn8ylpHFHOjTdiRJ6mU8uIkhkGLRSRvHvuoBqU/edit?usp=sharing
答案1
得分: 0
=INDEX(IF(LEN(C2:C),IFNA(VLOOKUP(C2:C,FILTER({Port!B:D,Port!A:A},REGEXMATCH(Port!C:C,"(?i)^gi|te")),{2,3,4},)),))
英文:
Can you try:
=INDEX(IF(LEN(C2:C),IFNA(VLOOKUP(C2:C,FILTER({Port!B:D,Port!A:A},REGEXMATCH(Port!C:C,"(?i)^gi|te")),{2,3,4},)),))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论