Lookup and copy coordinates (Excel 2007)

Topic

New Reply

Edited by HansV to provide subject more descriptive than “Excel 2007”

Hi, I would search for this but I have no idea what to search on. I have an excel file (12,000 rows) with short post code data ex (AB23) in column A in B & C I have the lat and long co-ordinates of that post code, in column D I have the full post code of members ex CM23 3SN (note the space). I would like if it is possible to automatically compare column D to A and then if a match is found copy the lat long data from B & C to G & H for that member. If any body can point me in the right direction I shall be grateful. Thanks gws.

Reply | Quote

Replies

I can’t figure out what you have and what you want from your description. Could you post a workbook that shows what you have and what you want?

Reply | Quote

Hi, and thanks for the offer to help, Attached is the excel file, I have had to remove all personnel details due to the data protection act in the UK. In Column A (Named code) is a short version of the full UK post code Column B and C hold the lat and long co-ordinates for that particular post code. In column D is a list of short post codes called outcode that belong to members of this list, I need to compare each field in D with the column A and if a match is found put the co-ordinates from the matching fields in B & C into the respective fields in F and G. I have done the first 3 by hand so that you can see what I need. I hope that is clear. Thanks G W S PS I have had to greatly reduce the number of post codes as the file was far to big to upload, there are now only 500 rows of post code data instead of 14,000.

Reply | Quote

In F2:
=VLOOKUP($D2,$A$2:$C$499,2,0)

In G2
=VLOOKUP($D2,$A$2:$C$499,3,0)

Autofill F2:G2 to F3:G whatever (adjust the ranges in the lookup range as desired…

Steve

Reply | Quote

Thank you so very much,That works a treat.

Reply | Quote