+ Reply to Thread
Results 1 to 11 of 11

Index Match with lookup values containing letters, numbers and spaces

  1. #1
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Index Match with lookup values containing letters, numbers and spaces

    Hi all,

    I'm having trouble with an index match formula. My lookup values look like one of these two types:

    092GSW015
    103P 345

    Whenever the formula hits the second value (with the space in the middle) it returns #N/A. I've spent a lot of time trying to figure out how to fix or use a different formula, but so far have had no luck. So now hoping someone smarter than me (not hard!), can help.

    Any suggestions?


    Note user level = beginner

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,117

    Re: Index Match with lookup values containing letters, numbers and spaces

    No problem with VLOOKUP or INDEX/MATCH for your search values.

    HTML Code: 

    Regards, TMS
    Last edited by TMS; 03-18-2014 at 06:45 PM.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Index Match with lookup values containing letters, numbers and spaces

    Hmm, yes, very strange. I can repeat your example in a new sheet as well. I wonder what I've done wrong.

    My array and lookup_array (for index and match formulas) are located in a separate workbook. I can't imagine that would be the problem. Cells are all formatted as "general" if that makes a difference. Strange.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,117

    Re: Index Match with lookup values containing letters, numbers and spaces

    How would you like us to diagnose the problem?

  5. #5
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Index Match with lookup values containing letters, numbers and spaces

    Not sure, but here's some more information that may be helpful. If I go to my test spreadsheet which I set up similar to your first response, then highlight column by clicking the "A" at the top, then click "Ctrl-F" and enter the search string "103P 345" and hit enter, it jumps to cell A2. Indicating it found the text string.

    However, in my working spreadsheets when I do the same thing, for any text string that has the space in the middle the search function returns "Microsoft Excel cannot find the data you're searching for."

    Perhaps I have a setting that needs to be changed?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,117

    Re: Index Match with lookup values containing letters, numbers and spaces

    Maybe you have leading or trailing spaces, either in the search string or the lookup table.

    Alternatively, the "space" in the middle may not be a true space but a non-printable character.

    For example, type: =CHAR(160) into cell A1. It will not display anything. Test the length of the cell =LEN(A1). Copy it and Paste Special | Values into cell A2. Check the length of that cell. What do you see? Looks like nothing but it has a single non-printable character.

    This typically happens with data from a web site.

    Try deleting the space in the middle of one of these cells and retyping it. Then search for it again.

    Regards, TMS
    Last edited by TMS; 03-18-2014 at 07:34 PM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,130

    Re: Index Match with lookup values containing letters, numbers and spaces

    Or you might have a double space in the middle (difficult to spot that).

    Pete

  8. #8
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Index Match with lookup values containing letters, numbers and spaces

    That was it! I used the trim function and now everything works properly. Thanks all, much appreciated!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,117

    Re: Index Match with lookup values containing letters, numbers and spaces

    You're welcome. Thanks for the rep.

    That was it!
    What was it? Trailing spaces? Leading spaces ...?



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  10. #10
    Registered User
    Join Date
    03-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Index Match with lookup values containing letters, numbers and spaces

    Not sure if it was trailing or leading spaces, but when I applied the trim function then copied and pasted the results overtop, the formulas all worked and the error notation disappeared. I've completed a rough spot check comparing the results manually to the data table and everything is correct. Very glad this is working now and can move on to the rest of my work!

    Cheers!

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,117

    Re: Index Match with lookup values containing letters, numbers and spaces

    Thanks for the clarification and update. Glad you have a resolution.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. Duplicate values returned using Index Match Lookup with Small()
    By jacob@thepenpoint in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2013, 10:48 AM
  3. [SOLVED] Lookup match index multiple values return other columns
    By martypocock in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 11-07-2012, 10:57 PM
  4. LOOKUP, MATCH, INDEX Question. (2 Search Values)
    By J00 in forum Excel General
    Replies: 1
    Last Post: 07-16-2012, 03:51 PM
  5. Lookup 2 possible values within INDEX/MATCH function.
    By Pete123abc in forum Excel General
    Replies: 2
    Last Post: 06-07-2011, 10:51 AM

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