单列中匹配对的vlookup
我有一个我们生产中所有站点的VPN隧道的电子表格。我正在尝试在Excel中创建一个表面的表，其中已经知道源和网络，目的地是两者的输出。从本质上讲，在网络列中，每个网络地址有2个（并且不超过2）个实例。可以说，我想创建第三列（目标输出）列来输出匹配对的另一半。我觉得这应该是某种vlookup，但我无法弄清楚如何格式化它。目标是这样做，而无需创建另一个辅助列或类似的东西。我知道我可以在Kutools中使用一些功能来执行此操作，但是我正在尝试保持工作表的动态，而不是依靠将静态编辑到表格上的宏。
| 源 | 网络 | 目的地 |
|---|---|---|
| Ashburn | 1.1.1.0 | 圣何塞 |
| 达拉斯 | 4.4.4.4.0 | 西雅图 |
| 西雅图 | 2.2.2.2.0 | 圣何塞 |
| | | （ |
| ） | 输出 | 法兰克福 |
| | 3.3.3.3.3.3.3.3.0 | |
| · | | |
| | | |
感谢您提供的任何帮助！
编辑：因此，我现在尝试使用index（）和match（）为此，我认为我越来越近了...
= index（$ a：$ b，match（b2，b：b：b，0）， 1）返回第一场比赛，这是本身。我如何修改我的比赛语句以选择第二场比赛？并且会搜索整个列表，然后再从头开始，直到找到第二个匹配？还是只有以某种方式订购时才能起作用？
谢谢！
I have a spreadsheet of all of the site-to-site VPN tunnels we have in production. I'm attempting to create a table like the following in Excel, where Source and Network are already known, and Destination is an output of the two. Essentially in the Network Column there are 2 (and no more than 2) instances of each network address. I would like to create a third column (the Destination OUTPUT) column to output the other half of a matching pair, so to speak. I feel like this should be some sort of VLOOKUP but I cannot for the life of me figure out how to format it. The goal would be to do it without needing to create another helper column or anything like that. I know I could use some functionality in Kutools to do it, but I'm trying to keep the sheet dynamic rather than relying on a macro that makes a static edit to the sheet.
| Source | Network | Destination (OUTPUT) |
|---|---|---|
| Ashburn | 1.1.1.0 | San Jose |
| Dallas | 4.4.4.0 | Seattle |
| Vancouver | 2.2.2.0 | San Jose |
| Frankfurt | 3.3.3.0 | Chicago |
| San Jose | 1.1.1.0 | Ashburn |
| Chicago | 3.3.3.0 | Frankfurt |
| San Jose | 2.2.2.0 | Vancouver |
| Seattle | 4.4.4.0 | Dallas |
Thanks for any help you can provide!!
EDIT: So I'm trying to use INDEX() and MATCH() for this now and I think I'm getting closer...
=INDEX($A:$B,MATCH(B2,B:B,0),1) returns the first match, which is just itself. How might I modify my MATCH statement to select the second match? And will this search the entire list and then start from the beginning again until it finds the second match? Or would it only work when ordered in a certain way?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您有办公室365：
您也可以在任何版本的Excel中使用：（如@jvdv所建议）
（正常输入）
If you have Office 365:
You can also use, in any version of Excel: (as suggested by @JvdV)
(Normally entered)
如果您没有动态数组，则可以在所有版本的Excel中使用，以作为数组公式输入：
< img src =“ https://i.sstatic.net/kirb0.png” alt =“屏幕截图说明了提议的公式的结果”>
This will work in all versions of Excel, entered as an array formula if you don't have dynamic arrays: