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.

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!

CodePudding user response：

This will work in all versions of Excel, entered as an array formula if you don't have dynamic arrays:

=INDEX($A$1:$A$9,MAX(IF((B2=$B$2:$B$9)*ROW($B$2:$B$9)=ROW(),0,(B2=$B$2:$B$9)*ROW($B$2:$B$9))))

CodePudding user response：

If you have Office 365:

C2: =FILTER($A$2:$A$9,(B2=$B$2:$B$9)*(A2<>$A$2:$A$9))

If you have earlier versions, try:

=INDEX($A$2:$A$9,-1 SUM(($B$2:$B$9=B2)*(A2<>$A$2:$A$9)*(B2=$B$2:$B$9)*ROW($A$2:$A$9)))

entered with ctrl shift enter.

Note: The -1 in the row argument should be changed depending on the row of the column names. In my example, it is row 1, hence -1