Town/City to lookup county

Closed
mohalloran Posts 2 Registration date Tuesday March 15, 2016 Status Member Last seen March 15, 2016 - Mar 15, 2016 at 12:53 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 17, 2016 at 12:00 PM
Hello,

I have a spreadsheet that what i am trying to accomplish is that when an address is typed into a column it will look up and match the town and will come back with the county it is in. I have two pages Master and Town & City. Master contains the address (B) and county (C) column. Town & City has the table that contains a list of towns and cities (A) and the corresponding county (B). I have tried various if then statements and lookups and can't quite seem to get it. Any help would be amazing.

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 15, 2016 at 01:03 PM
Hi Mohalloran,

Try the VLOOKUP formula:
=VLOOKUP(The address you want to lookup, the matrix addresses and countries are located, 2, 0)

Best regards,
Trowa
0
mohalloran Posts 2 Registration date Tuesday March 15, 2016 Status Member Last seen March 15, 2016
Mar 15, 2016 at 01:35 PM
The formula I have typed in is =vlookup([@[Address]],'Town &City'!A:B, 'Town & City'!B:B,2) and it just keeps returning #REF
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 17, 2016 at 12:00 PM
Hi Mohalloran,

Let's say the address you want to look up is in sheet 'Town &City' C2.
The matrix with addresses and countries are located in sheet 'Master' B:C.
Then the formula in sheet 'Town &City' D2 will look like:

=VLOOKUP(C2,'Master'!B:C,2,0)

The value in C2 will be looked up in column B of Master sheet. The corresponding value in column C of Master sheet will be returned, hence the 2 (second column in the matrix) after the 2nd comma. The final zero means that the addresses in column B of Master sheet don't have to be in alphabetical order.

Best regards,
Trowa
0