| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Post code lookup
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Post code lookup
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 Paul Williams wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Post code lookup
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. "Pete_UK" wrote in message oups.com... 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 Paul Williams wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Post code lookup
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 Paul Williams wrote: 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. "Pete_UK" wrote in message oups.com... 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 Paul Williams wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Post code lookup
Thanks very much Pete. Works fine.
Paul. "Pete_UK" wrote in message oups.com... 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 Paul Williams wrote: 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. "Pete_UK" wrote in message oups.com... 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 Paul Williams wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Post code lookup
Glad to help - thanks for feeding back.
Pete Paul Williams wrote: Thanks very much Pete. Works fine. Paul. "Pete_UK" wrote in message oups.com... 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 Paul Williams wrote: 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. "Pete_UK" wrote in message oups.com... 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 Paul Williams wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy and use vba code | Excel Discussion (Misc queries) | |||
| Is it possible to have a multiple lookup formula. | Excel Discussion (Misc queries) | |||
| Using other workbooks.. | Excel Worksheet Functions | |||
| Post Code search | New Users to Excel | |||
| Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Linear Mode
