Use vlookup to bring back data from second occurrence of the value

[TinkieO]

I have a list of people in an excel spreadsheet (column 1).
In column 2 is the name of the person who introduced the person in column 1
to the list (the introducers name also appears in column 1).
Column 3 is a repeat of column 1 (for vlookup to work).
In other words column contains the same name more than once.
In Column 4 I want the name of the first person introduced by the
introducer, which a simple vlookup using column 2 & 3 works.
In Column 5 I want the name of the second person introduced by the
introducer, i.e. I want vlookup to bring back the value in column 3 for the
second row in which the value in column 1 appears in column 2.
In Column 6 I want the name of the third person etc
How do I do this?

 

[Jacob Skaria]

You don't need the temporary Col C. If you are looking to get data as shown
below. ColC retrieves the 1st matching record of John ColD retreives the
second and so on. Please note that this is an array formula. Within the cell
in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

Try this formula in C1 and copy across to D1, E1 etc;

=INDEX($A:$A,SMALL(IF($B$1:$B$1000<>$B1,"",($B$1:$B$1000=$B1)*ROW($B$1:$B$1000)),COLUMN(A1)))

Col A Col B Col C Col D Col E
A John A B C
E Mary
F Serra
B John
G Phil
H Ben
C John

If this post helps click Yes