Post code lookup

  • Thread starter Thread starter Paul Williams
  • Start date Start date
P

Paul Williams

Is it poosible to use this look up table

B7 Bolton
CH56 Cheshire
HU7 Hull
600 of these


to add to the full post code to create this

B7 1QR Bolton
CH56 8SX Cheshire
HU7 9FG Hull
B7 2QR Bolton
CH56 5fX Cheshire
HU7 8SG Hull
19000 of these


Any help would be greatly appreciated.

Paul.
 
Do you mean that you currently have:

B7 1QR
CH56 8SX
HU7 9FG
B7 2QR
CH56 5FX
HU7 8SG
19000 of these

and you want to add the locations from your lookup table to another
column adjacent to the full postcode?

Pete
 
Yes that is right. It looked ok when i pasted it in but looked like this
when i posted it to the group.

The B7 and Bolton are also seperate cells.
 
OK, Paul, suppose your lookup table occupies G1 to H600, and that you
have your full postcode in column A from A1 to A19000. Put this formula
in B1:

=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),G$1:H$600,2,0)

Adjust the ranges to suit your situation and then copy the formula down
- a quick way is to select B1 and then double-click the fill-handle
(the small black square in the bottom right corner of the cursor).

You can then (with the formula cells still highlighted) click <copy>
then Edit | Paste Special | Values (check) OK and <Esc> to fix the
values.

Hope this helps.

Pete
 
Thanks very much Pete. Works fine.

Paul.


Pete_UK said:
OK, Paul, suppose your lookup table occupies G1 to H600, and that you
have your full postcode in column A from A1 to A19000. Put this formula
in B1:

=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),G$1:H$600,2,0)

Adjust the ranges to suit your situation and then copy the formula down
- a quick way is to select B1 and then double-click the fill-handle
(the small black square in the bottom right corner of the cursor).

You can then (with the formula cells still highlighted) click <copy>
then Edit | Paste Special | Values (check) OK and <Esc> to fix the
values.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code hang 1
Create Bar code Sheet w/lookups, index/match 3
Lookup help 6
V-lookup 6
IFs with VLOOKUPs 5
Lookup sort of thing! 2
Complicated lookup 2
Nested Lookup !! ? 2

Back
Top