Compare 2 columns and if there is duplicates extract data from corresponding column into a new columns.

[ando000]

My spreadsheet.

A	B	C	D	E	F	G	H
s1	street1		street1
s2	street2		street2
s3	street2		street3
s4	street2		street4
s5	street2		street5
s6	street2
s7	street3
s8	street3
s9	street4
s10	street5

<tbody>
</tbody>

I require a formula to compare columns B and D for a match up, and if a match up does occur print the corresponding result from Column A into E. However, if there are multiple duplicates between B and D print into new columns.

End result of what I'd like.

A B C D E F G H s1 street1 street1 s1 s2 street2 street2 s3 s4 s5 s6 s3 street2 street3 s7 s8 s4 street2 street4 s9 s5 street2 street5 s10 s6 street2 s7 street3 s8 street3 s9 street4 s10 street5 <tbody> </tbody>

<tbody>
</tbody>

I have browsed the forums for previously solved solutions and this is the closest i've found.
http://www.mrexcel.com/forum/excel-...l-print-third-column-without-replacement.html

I have been using this formula and it worked amazingly but it does not account for duplicates into new columns. '=INDEX(A:A,SMALL(IF(B$2:B$10=D2,ROW(B$2:B$10)),COUNTIF(D$2:D2,D2)))'

Thanks for taking the time to read this!

 

[azumi]

Put in E2:
=IFERROR(INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=$D2,ROW($A$2:$A$11)-ROW($A$2)+1),COLUMNS($A$1:A1))),"")

this array formula when ENTER you need to press CTRL-SHIFT-ENTER button together and copied down and cross as necessary

 

[ando000]

Azumi.. you are my hero. <3
A song for you
WIND BENEATH MY WINGS (Lyrics) - BETTE MIDLER - YouTube