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

#### ando000

##### New Member
 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!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### azumi

##### Well-known Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back