VLookup to display area using postcode

[DanaScully]

Hi

I'm a new member here, nice to meet all of you

I am trying to create a VLookup function which will give an area by using a postcode (UK). I have followed directions on another website by searching 'determining a state from an area code' but I am getting a #VALUE ! error when using their suggested formula.

In the spreadsheet, I have created a named table (LondonPostcodes) with one column for my postcodes and one for the area name. For example, I would like the postcode SW1A 1AA to return the area 'London' in the adjacent cell. This is my formula:

=VLOOKUP(VALUE(LEFT(L32,6)),LondonPostcodes,2,FALSE)

Can anyone see where I might be going wrong? I am a novice to intermediate user and I am using Excel 2010 on Windows.

Thanks in advance for any replies.

DS

 

[FormR]

Hi, welcome to the forum!

What do you have in cell L32 and what is the corresponding lookup value in column 1 of LondonPostcodes?

 

[DanaScully]

Hi FormR, thanks for your reply

I have a 6 character postcode in cell L32. I have removed the space in the middle so it's formatted as SW1 1AA in case that was the issue. I've also removed the spaces from the postcodes in my corresponding table so they are all definitely the same. I just want my formula to indicate if a postcode is in London or display 'FALSE' or similar if it's outside London.

I hope I've answered your question; if not please let me know and I'll try again.

DS

 

[FormR]

I hope I've answered your question; if not please let me know and I'll try again.

Hi, I was hoping for some actual examples of the L32 values and what you are trying to match those against in the lookup table - but it sounds like they are the same, in which case it's simply:

=VLOOKUP(L32,LondonPostcodes,2,FALSE)

 

[DanaScully]

Hi, I was hoping for some actual examples of the L32 values and what you are trying to match those against in the lookup table - but it sounds like they are the same, in which case it's simply:

=VLOOKUP(L32,LondonPostcodes,2,FALSE)

=VLOOKUP(VALUE(LEFT(L32,6)),LondonPostcodes,2,FALSE)

Thank you for your reply, your formula worked perfectly! I am very pleased and this will help me so much on a daily basis.

DS

 

[DanaScully]

Sorry, please ignore the formula in my above post. I pasted it in to see how different your formula was to the one in my original post and forgot to delete it before I submitted.

 

[FormR]

No worries - glad to help