Find and Replace-esque formula required!

Hi All,

I'm hoping someone can help with this quandry,

I'm trying to substitute every value in A1 for its respective value in A3 BUT crucially I still need A1 to be able to be edited? I've tried to illustrate this in the below example;

AB1 2 N 73 73LB 238011
AB1 4 N 73 73LB 238011
AB1 5 N 73 73LB 238011
AB1 6 N 73 73LB 238011
AB1 8 N 73 73LB 238011
AB10 N 73 73LB 238011
AB10 1 N 73 73LB 238011

Basically I need something so that when a user enters any of the postcodes for Aberdeen as above (though the postcodes in my .xls are for the whole of the UK), then the corresponding internal area code is shown (in this instance 73, but again will change dependent on postcode) in another column along from it.

I known the simple '=A3' in the A1 would substitute them but I really need the ability to input postcodes and have the sheet change them? (would the two parameters need to be elsewhere in the sheet and act as a 'bank'?) I'd envisaged it to be a 'find and replace' type of solution in my (non-technical!) terms

I know I perhaps haven't explained myself very well in terms of excel terminology, thus I didn't known how to search for a solution to this problem but if someone could shed any light on it or point me in the direction of the answer if this topic has already been discussed, that'd be great!

Many thanks

Dave

I would consider changing the layout of your sheet so that the cell the postcode is entered in is separate. So for example:
-user enters postcode in A1
-area code is then looked up and put in A3
-Combination of the two is then put in A5.

Thanks Ragulduy,

That's what I've done but when I've copied the table it's all merged. Essentially I'd like the postcode to be automatically changed to the corresponding branch along, without the user having to do a manual lookup. The manual lookup is what they're having to do at the minute and as they're entering a lot of postcodes in random order, they're wasting a lot of time having to look them up manually.

Hope this makes it a bit clearer what I'm after

Thanks

Dave

I'd suggest posting an example workbook so we can see what your data looks like.

Postcode to Branch Lookup (Edited).xlsx

I've attached it below as a sample. In this instance column A needs to correspond to column B, so that a user can enter any postcode as contained in column A and it will replace it with the data in column B.

Please note this is the the database of info, ideally I'd like to have this info contained in another sheet so that when a user enters a postcode on a separate workbook, it automatically populates the next field along with the internal branch identifier?

Thanks again for you continued help