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:

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Original ID[/TD]
[TD]New ID[/TD]
[TD]Original ID[/TD]
[TD]New ID[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]004[/TD]
[TD]004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]005[/TD]
[TD]004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]007[/TD]
[TD]007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006[/TD]
[TD]010[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]007[/TD]
[TD]011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]008[/TD]
[TD]012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]009[/TD]
[TD]013[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Original ID[/TD]
[TD]New ID[/TD]
[TD]Original ID[/TD]
[TD]New ID[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]001[/TD]
[TD]002[/TD]
[TD]002[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]002[/TD]
[TD]002[/TD]
[TD]002[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]004[/TD]
[TD]004[/TD]
[TD]005[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]005[/TD]
[TD]004[/TD]
[TD]005[/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]007[/TD]
[TD]007[/TD]
[TD]011[/TD]
[/TR]
[TR]
[TD]006[/TD]
[TD]010[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]007[/TD]
[TD]011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]008[/TD]
[TD]012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]009[/TD]
[TD]013[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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:
[TABLE="width: 297"]
<tbody>[TR]
[TD]Excel 2007

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Original ID[/TD]
[TD="bgcolor: #FAFAFA, align: center"]New ID[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Original ID[/TD]
[TD="bgcolor: #FAFAFA, align: center"]New ID[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]5[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: center"]11[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D2[/TH]
[TD="align: left"]=INDEX($A$2:$B$10,MATCH(C2,$A$2:$A$10,0),2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D3[/TH]
[TD="align: left"]=INDEX($A$2:$B$10,MATCH(C3,$A$2:$A$10,0),2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D4[/TH]
[TD="align: left"]=INDEX($A$2:$B$10,MATCH(C4,$A$2:$A$10,0),2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D5[/TH]
[TD="align: left"]=INDEX($A$2:$B$10,MATCH(C5,$A$2:$A$10,0),2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D6[/TH]
[TD="align: left"]=INDEX($A$2:$B$10,MATCH(C6,$A$2:$A$10,0),2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Regards,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]