+ Reply to Thread
Results 1 to 9 of 9

lookup alternative

  1. #1
    Registered User
    Join Date
    07-20-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    67

    lookup alternative

    I have a spreadsheet that looks up a area code from the postcode. I am using vlookup but it is giving incorrect answers. it works unless the postcode contains only 1 letter in the first set of characters. ie ne26 works bu n1 doesnt. is there a alternative to lookup?

    I have attahced the spreadsheet.
    Attached Files Attached Files
    Last edited by toclare84; 08-31-2010 at 07:34 AM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: lookup alternative

    I can't download 2007 macro-enabled spreadsheets... but I think I could help if you could save it as an xlsx.
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  3. #3
    Registered User
    Join Date
    07-20-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: lookup alternative

    Hi,

    I have changed the format for you. thanks for your help!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: lookup alternative

    What result were you expecting for N1? It doesn't appear to be on your postcodes sheet.

  5. #5
    Registered User
    Join Date
    07-20-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: lookup alternative

    there is no N1, N2, N3 as all the postcodes begining with N should have the code G. see i can get it to find an exact match, but i then need for the formula to march to the letter if there is no exact match. does that make sense?

    so lets say there is a N33 in my list and a N.

    when i enter N33 it will find it as it is an exact match.

    but if i enter N20 (as it doesnt appear in my list it should find the N match and bring back that result.

  6. #6
    Registered User
    Join Date
    07-20-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    67

    Postcode match

    Hi,

    I have a postcode finder that matches the postcode to an area. it works for exact matches, but i need to add to this code:

    =VLOOKUP(LEFT(B3,IF(ISNUMBER(MATCH(LEFT(B3,4),POSTCODES!A:A,0)),4,IF(ISNUMBER(MATCH(LEFT(B3,3),POSTCODES!A:A,0)),3,2))),POSTCODES!A:B,2,0)

    to say that if there is not an exact match, then to match B6 to the postcodes instead. so the formula would run again but instead of using B3 it would use B6.
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Postcode match

    If I've understood then given use of XL2007

    Please Login or Register  to view this content.

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: lookup alternative

    Why have you started a new thread on this subject?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  9. #9
    Registered User
    Join Date
    07-20-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: lookup alternative

    thats great it works brilliantly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1