Vlookup to find Second INstance

[comish4lif]

I have a table of relationships - where an item can relate to one or
more items. Specifically, these are systems and their interfaces.

For example, System A relates to (shares info/data) with System B which
also has a relationship with System C. I want to generate something
that looks like the previous statement. Column A would have System A
(the lookup value) and Column B would have the result of a vlookup
based on the value in column A. Column C would then vlookup from Column
B and so on.

The issue here is that System A relates to multiple systems. How can I
rig a vlookup to find the second, third, fourth, etc, instances?

Sample Data
A - B - A
A - C - B
A - D - E
A - E - B
B - A - C
B - E - H
E - H

 

[Biff]

Hi!

You can't use Vlookup for multiple instances.

Try something like this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(B1:B10,SMALL(IF(A1:A10="A",ROW($1:$10)),Instance_Number))

Where:

B1:B10 is the data to return
A1:A10 is the range that contains the Lookup_Value "A".
ROW($1:$10) is the SIZE of the range
Instance_Number is the instance to look for

Biff