Results 1 to 9 of 9

Thread: Vlookup based on contents of a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    122
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup based on contents of a cell

    I've gotten a list of post codes and their counties.

    This is how the data is presented:

    Postcode area Postcode districts Post town Former postal county
    AB AB10, AB11, AB12, AB15, AB16,
    AB21, AB22, AB23, AB24, AB25,
    AB99non-geo
    Aberdeen (Aberdeenshire)
    I need to do some type of lookup (I think), where the first part of a full post code being AB22 would show Aberdeenshire

    Is there a type of formula I could use to do this?

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,109
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Hi, you could extract the postcode area from a full postcode with a formula like this:

    Excel 2013/2016
    AB
    1Full postcodePostcode area
    2AB22 0AAAB

    Sheet1



    Worksheet Formulas
    CellFormula
    B2=LEFT(A2,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)-1)



    You could then do a standard VLOOKUP() to the get post town or county.
    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Posts
    122
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Hi

    Thanks for the super quick reply

    The above was a snippet of every post code in the UK

    Would you formula still work?

    I can't do the lookup on "AB" in the above example as some Postcode Areas have more than County inside them

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,109
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Quote Originally Posted by geordie_ben View Post
    The above was a snippet of every post code in the UK
    Hi, a slightly bigger snippet, along with a few examples of the inputs and expected results be helpful. But pending that - here's another stab.

    Excel 2013/2016
    ABCDEFG
    1Postcode areaPostcode districtsPost townFormer postal countyFull postcodetown
    2ABAB10, AB11, AB12, AB15, AB16, AB21, AB22, AB23, AB24, AB25, AB99non-geoAberdeen(Aberdeenshire)AB22 0AAAberdeen

    Sheet1



    Worksheet Formulas
    CellFormula
    G2=VLOOKUP("*"&LEFT(F2,FIND(" ",F2&" ")-1)&"*",B:C,2,0)

    [code]your code[/code]

  5. #5
    Board Regular
    Join Date
    Jul 2009
    Posts
    122
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Hi

    The list of information is from here - https://en.wikipedia.org/wiki/List_o...United_Kingdom

    This is a decent snippet:

    B B1, B2, B3, B4, B5, B6, B7, B8, B9,
    B10, B11, B12, B13, B14, B15, B16, B17, B18, B19,
    B20, B21, B23, B24, B25, B26, B27, B28, B29,
    B30, B31, B32, B33, B34, B35, B36, B37, B38,
    B40, B42, B43, B44, B45, B46, B47, B48,
    B99non-geo
    Birmingham (West Midlands)
    B B49,
    B50
    Alcester Warwickshire
    B B60, B61 Bromsgrove Worcestershire
    B B62, B63 Halesowen West Midlands
    B B64 Cradley Heath West Midlands
    B B65 Rowley Regis West Midlands
    B B66, B67 Smethwick West Midlands
    B B68, B69 OLDBURY West Midlands
    B B70, B71 West Bromwich West Midlands
    B B72, B73, B74, B75, B76 Sutton Coldfield West Midlands
    B B77, B78, B79 TAMWORTH Staffordshire
    B B80 STUDLEY Warwickshire
    B B90, B91, B92, B93, B94 Solihull West Midlands
    I have a list of customer post codes, and I need to establish which County they're in, which is Column D in the above example

    Example:

    Postcode is B75 1AZ

    I've done a text to columns, delimited on space to get the first part of the Post Code (I've manually checked them and they're all good - phew!)

    The formula would then bring back the County as West Midlands

    Hopefully this helps

  6. #6
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,109
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Quote Originally Posted by geordie_ben View Post
    I've done a text to columns, delimited on space to get the first part of the Post Code
    Hi, you can try this:

    Excel 2013/2016
    ABCDEFG
    1BB1, B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, B21, B23, B24, B25, B26, B27, B28, B29, B30, B31, B32, B33, B34, B35, B36, B37, B38, B40, B42, B43, B44, B45, B46, B47, B48,B99non-geoBirmingham(West Midlands)
    2BB49, B50AlcesterWarwickshireB75West Midlands
    3BB60, B61BromsgroveWorcestershire
    4BB62, B63HalesowenWest Midlands
    5BB64Cradley HeathWest Midlands
    6BB65Rowley RegisWest Midlands
    7BB66, B67SmethwickWest Midlands
    8BB68, B69OLDBURYWest Midlands
    9BB70, B71West BromwichWest Midlands
    10BB72, B73, B74, B75, B76Sutton ColdfieldWest Midlands
    11BB77, B78, B79TAMWORTHStaffordshire
    12BB80STUDLEYWarwickshire
    13BB90, B91, B92, B93, B94SolihullWest Midlands

    Sheet1



    Worksheet Formulas
    CellFormula
    G2=LOOKUP(2,1/(ISNUMBER(SEARCH(F2&",",$B$1:$B$999&","))),$D$1:$D$999)

    [code]your code[/code]

  7. #7
    Board Regular
    Join Date
    Jul 2009
    Posts
    122
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup based on contents of a cell

    That's worked perfectly, thanks!!

    Out of interest, why did you change the formula?

  8. #8
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,109
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Quote Originally Posted by geordie_ben View Post
    Out of interest, why did you change the formula?
    Hi, I realised that the VLOOKUP() with the wildcards is not robust. It would match B2 with B22 if B22 happened to occur in a row before B2 for example.
    [code]your code[/code]

  9. #9
    Board Regular
    Join Date
    Jul 2009
    Posts
    122
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup based on contents of a cell

    Ah, gotcha

    I think luckily they're all in alphabetical order.

    Thanks for your help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •