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")))))))))))))))))
Gwynneth TaylorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Pierre CorneliusConnect With a Mentor Commented:
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 HensonFinance AnalystCommented:
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
All Courses

From novice to tech pro — start learning today.