Find and Replace Strings

[Dave_Holland]

Hi All,

I'm trying to carry out a find and replace type function with a table of postcodes and internal company identifier codes. Basically I would like the user to be able to type in the postcode in a workbook (rather than using ctrl+F and then manually doing it) and then in the next column along it auto-populates the internal identifier. The sample table is below, the table I imagine would have to be located somewhere in the workbook?

I would greatly appreciate any help as so far as simple '=FIND' '=SEARCH' of course don't work.

Thanks in advance

Dave

[TABLE="width: 96"]


<colgroup><col style="width: 48pt;" span="2" width="64">
<tbody>[TR]

[TD="class: xl63, width: 64, bgcolor: transparent"]AB1 1[/TD]

[TD="width: 64, bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB1 2[/TD]

[TD="bgcolor: transparent, align: right"]72[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB1 4[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB1 5[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB1 6[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB1 8[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB10[/TD]

[TD="bgcolor: transparent, align: right"]72[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB10 1[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB10 2[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]AB10 6[/TD]

[TD="bgcolor: transparent, align: right"]73[/TD]

[/TR]


</tbody>[/TABLE]

 

[konew1]

With your data in A1:b10, and the code you want to lookup in E3

=INDEX(B1:B10,MATCH(E3,A1:A10,0))