+ Reply to Thread
Results 1 to 4 of 4

Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

  1. #1
    Registered User
    Join Date
    03-24-2013
    Location
    Apple Valley, MN
    MS-Off Ver
    Excel 2010
    Posts
    29

    Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

    I have a list of Chinese names in column A, in column B I pasted a formula which will look at the first character A1 and then index match to come up with the corresponding pinyin, then it looks for the 2nd character and index matches and returns the corresponding pinyin, then it looks for the third character, then the fourth but if there are only 2 or 3 characters in the name I get a #N/A error so I need this formula which is set up for the maximum of four characters to work even if there are only two or three characters.
    Please Login or Register  to view this content.
    I tried to add "IF(ISERROR" (I'm using EXCEL 97 so I can't use the IFERROR function) but it said there was an error in the formula.
    I have attached a greatly simplified workbook that demonstrates the problem
    concatenate problem.xls

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,130

    Re: Concatenated INDEX MATCH formula that won't give error that can index match 2-4 string

    hi bkwins. it is still ok if there are only 2-4 characters. otherwise, the formula would get too messy & you probably need VBA. you can try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the one in B5 doesnt match your desired answers because there are 2 "L"s in column F. VLOOKUP or MATCH takes the 1st instance

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    03-24-2013
    Location
    Apple Valley, MN
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Concatenated INDEX MATCH formula that won't give error that can index match 2-4 string

    benishiryo, Thanks so much! Before I mark this solved can you tell me how to change the above formula so it gives me a blank cell if there is no name in a cell in column A? There would never be a situation where there is only one character. Thanks!

  4. #4
    Registered User
    Join Date
    03-24-2013
    Location
    Apple Valley, MN
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Concatenated INDEX MATCH formula that won't give error that can index match 2-4 string

    Never mind I just figured it out myself. The following code will handle a blank cell on the left or one with just one character also.
    Please Login or Register  to view this content.
    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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