+ Reply to Thread
Results 1 to 9 of 9

INDEX / MATCH or VLOOKUP

  1. #1
    Registered User
    Join Date
    04-11-2009
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    8

    INDEX / MATCH or VLOOKUP

    Hello, I used Excel for years but have not for a while and am back to being a newbie. Basically, I have created a list of two-letter codes to match the names of the 29 Indian States, e.g. AndhraPradesh AP, Bihar BH etc. Now there is a humongously long column of election-data (something like 600 million people will vote over the next month or so) which has Andhra Pradesh appearing say 700 times followed by Bihar appearing 200 times etc. I want the latter list to check against the former list and yield the initials AP for Andhra Pradesh and BH for Bihar etc. Have sweated all day with LookUp, If, Match etc without success. Any help gratefully received.

    I was younger when I was an excel expert, now I am rusty ..
    Last edited by HVFTBMN; 04-23-2009 at 09:29 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: A really easy question which will help the biggest democratic exercise in the wor

    Can you write few examples?

  3. #3
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: A really easy question which will help the biggest democratic exercise in the wor

    Would you post a small sample workbook with expected result?

    This will help to understand your requirements better


    cheers, francis

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: A really easy question which will help the biggest democratic exercise in the wor

    Per forum rules please rephrase the title of your thread to something more suitable - perhaps along the lines of: "INDEX / MATCH or VLOOKUP ?"

  5. #5
    Registered User
    Join Date
    04-11-2009
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: A really easy question which will help the biggest democratic exercise in the wor

    AP Andhra Pradesh
    AR Arunachal Pradesh
    AS Assam
    BH Bihar
    CT Chattisgarh
    DL Delhi
    GJ Gujarat
    GO Goa
    HP Himachal Pradesh
    HA Haryana
    JH Jharkhand
    JK Jammu & Kashmir
    etc is the kind of list I created...
    then




    Andhra Pradesh
    Andhra Pradesh
    Arunachal Pradesh
    Arunachal Pradesh
    Assam
    Assam
    Bihar
    Bihar
    Goa
    Gujarat
    Gujarat

    is the kind of data in the other list.
    Iw ant the latter list to go one by one against the former list, and give me the appropriate two-letter abbreviation. It must be easy but I am exceptionally dense despite a PhD , books etc

    Apologies for the title, I thought I was within the rules, please feel free to change the title if I don;t figure out how to do it myself.

    Many thanks
    Last edited by VBA Noob; 04-11-2009 at 12:27 PM.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: A really easy question which will help the biggest democratic exercise in the wor

    You must define somewhere ALL states and their shorts...

    Look on this sheet:

    Book2.xlsx

    With more defined states you'll need to extend range in VLOOKUP function)
    Last edited by zbor; 04-11-2009 at 12:27 PM.

  7. #7
    Registered User
    Join Date
    04-11-2009
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    8

    "INDEX / MATCH or VLOOKUP ?"

    Have changed title as requested.

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: INDEX / MATCH or VLOOKUP

    you may want to allow for using and or & so put them in your list with both.
    heres as zbor's with all states and territories and their codes and allowance for &/and
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Registered User
    Join Date
    04-11-2009
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: INDEX / MATCH or VLOOKUP

    Many thanks to Martin Wilson and any others who thought about this problem. I assume it is okay to acknowledge this forum if the data get published in due course.

+ 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