Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

IF Statement

I am new to writing excel statements and trying to filter out scottish postcodes from over 3500 records to give the value "Scotland" or England.  My statment is incorrect - can you please help?

=IF(L2="KS",Scotland”,L2="KA",”Scotland”,L2="TD",”Scotland”,L2="DG",”Scotland”,L2="EH",”Scotland”,L2="KY",”Scotland”,L2="FK",”Scotland”,L2="AB",”Scotland”,IF(L2="G",”Scotland”,IF(L2="PA",”Scotland”,IF(L2="IV",”Scotland”,IF(L2="ML",”Scotland”,IF(L2="EH",”Scotland,IF(L2="KW",”Scotland”,IF(L2="ZE",”Scotland”,IF(L2="DD",”Scotland”,IF(L2="PH","Scotland","England")))))))))))))))))
0
Gwynneth Taylor
Asked:
Gwynneth Taylor
1 Solution
 
Pierre CorneliusCommented:
It would be better to use a VLookup function. Create a list with 2 columns i.e. PostCode, Country. I suggest you put it in a table. Your function would then look something like:

=VLookup(L2, [YourTableName e.g Table1], 2, 0)

I also found this link which may be helpful. You can get all post codes there as a download:
https://www.doogal.co.uk/UKPostcodes.php

I also attach an example
vlookupExample.xlsx
0
 
Rob HensonIT & Database AssistantCommented:
A lookup table would be much easier to maintain but you could use the OR function instead:

=IF(OR(L2="KS",L2="KA",L2="TD",L2="DG",L2="EH",L2="KY",L2="FK",L2="AB",L2="G",L2="PA",L2="IV",L2="ML",L2="EH",L2="KW",L2="ZE",L2="DD",L2="PH"),"Scotland","England")

Does L2 contain the whole postcode or just the first characters?
0
 
Gwynneth TaylorAuthor Commented:
Excellent quick response and problem solved thank you.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now