Formula for searching for values in a column based on another column

[fotherga]

I'm relatively inexperienced in excel, and am hoping there is a formula or macro I can use for this. Here is a sample of my sheet:

Original ID	New ID	Original ID	New ID
001	001	002
002	002	002
003	004	004
004	005	004
005	007	007
006	010
007	011
008	012
009	013

<tbody>
</tbody>

What I need to do is for each value in column 3, search column 1. When found in column 1, find the value in column 2 that is in the same row as the value in column 1. Then take this value from column 2, and paste it in column 4, in the same row as the started value of column 3.

Here is the resulting table:

Original ID	New ID	Original ID	New ID
001	001	002	002
002	002	002	002
003	004	004	005
004	005	004	005
005	007	007	011
006	010
007	011
008	012
009	013

<tbody>
</tbody>

All value are sorted, and are unique in column 1 and 2 but can be repeated in column 3 and 4. I can do this manually, but have a few thousand records to go through. Are there any suggestions?

 

[abmati]

Here you can use INDEX MATCH formula as under:

Excel 2007 A B C D 1 Original ID New ID Original ID New ID 2 1 1 2 2 3 2 2 2 2 4 3 4 4 5 5 4 5 4 5 6 5 7 7 11 7 6 10 8 7 11 9 8 12 10 9 13 <colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead> </thead><tbody> </tbody> Sheet1​ Worksheet Formulas Cell Formula D2 =INDEX($A$2:$B$10,MATCH(C2,$A$2:$A$10,0),2) D3 =INDEX($A$2:$B$10,MATCH(C3,$A$2:$A$10,0),2) D4 =INDEX($A$2:$B$10,MATCH(C4,$A$2:$A$10,0),2) D5 =INDEX($A$2:$B$10,MATCH(C5,$A$2:$A$10,0),2) D6 =INDEX($A$2:$B$10,MATCH(C6,$A$2:$A$10,0),2) <thead> </thead><tbody> </tbody> <tbody> </tbody> Regards,

<tbody>
</tbody>