Can't return multiple values of same input using VLOOKUP/INDEX/MATCH

Question

I've tried searching online to solve this problem but cannot seem to find an appropriate solution to this problem I have.

I am trying to find all of the MAC addresses connected to each Switch in a network.

I am doing this in excel and have the following sample data:

    +--------------+------------+--------+
    | Mac Address  |   Switch   |  Port  |
    +--------------+------------+--------+
    | 00144f601fdf | 6553DC-HA1 | Gi4/42 |
    | 00144f601fdf | 6554DC-LA1 | Gi6/1  |
    | 00144f601ff2 | 1123DC-MA1 | Gi8/34 |
    | 00144f601ff2 | 6554DC-LA1 | Gi6/1  |
    | 00144f601ff3 | 1123DC-MA1 | Gi8/35 |
    | 00144f601ff3 | 6554DC-LA1 | Gi6/1  |
    | 00144f685d38 | 1123DC-MA1 | Gi8/44 |
    | 00144f685d38 | 6554DC-LA1 | Gi5/1  |
    +--------------+------------+--------+

I have tried using VLOOKUP but obviously that only returns the first value, i've tried playing around with INDEX and MATCH but I haven't got anywhere helpful.

Ideally I'd like to see:

     00144f601fdf = 6553DC-HA1 
                  = 6554DC-LA1
     00144f601ff2 = 1123DC-MA1
                  = 6554DC-LA1

And so on... any help on this matter or suggestions for potential solutions would be greatly appreciated!

Is it always going to be two results? – Robin Gertenbach Nov 16 '15 at 09:54 — Robin Gertenbach, Nov 16 '15 at 09:54

mike mckechnie · Answer 1 · 2015-11-16T10:19:25.943

Your example "I'd like to see" shows all of the switches for each mac address, but your request is for "all of the MAC addresses connected to each Switch."

Assuming your example is what you want to see, then you can insert a column in Excel between the "MAC Address" and "Switch" columns and use a formula in the new column to show MAC addresses only when they change.

If the rows are already sorted by MAC Address, and MAC Address is column A, the formula is

=IF(A2=A1,"",A2)

Just paste that in each cell of the new column and it will how the MAC Address entry only the first time it appears - the result will look like your example above.

fyi if you want a more compressed result, use unique() and transpose() with (optionally) concatenate(). Example at this Google sheet.

score 0 · Answer 2 · answered Nov 16 '15 at 10:39

If the data is structure like in your example I recommend mike's answer.
If it is unordered you can use the following approach which will always return two results:
In D1: =VLOOKUP(A12,$A$2:$C$9,2,FALSE) (fairly obvious)
In D2:

=VLOOKUP(A12,
         OFFSET($A$2,
                MATCH(A12,$A$2:$A$9,0),
                0,
                COUNTA($A$2:$A$9) - MATCH(A12,$A$2:$A$9,0),
                2),
         2,
         FALSE)

This offsets the range to after the first matched MAC address and resizes it to the end of the supplied range (In case you have more stuff below) and runs the VLOOKUP on that.

Can't return multiple values of same input using VLOOKUP/INDEX/MATCH

2 Answers2