+ Reply to Thread
Results 1 to 6 of 6

Index and Match with Wildcards

  1. #1
    Registered User
    Join Date
    11-08-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2013/10/07/03
    Posts
    33

    Index and Match with Wildcards

    Good morning all,

    Ok so once again i am sure my issue can be solved with a good use of match and index (starting to learn them slowly).

    Essentially what I am trying to do today is match a static list of employee numbers (The list will grow over time though), with an employee number in the middle of an array in another spreadsheet. The issue being - the employee numbers in the second spreadhseet generally have a random collection of numbers and letters before the 6 digit number. So for example - Employee number sheet 1 is 123456 - sheet 2 could be gt00123456.

    But the purpose of matching the numbers is to find the matching values to fill columns. Since i am terrible at explaining things, ive attached a spreadsheet example.

    Couple notes - From what i can tell the sheet 2 employee numbers seem to have last 6 digits as the actual employee numbers so possibly use of "right" instead of wildcard?
    The 2nd sheet will be updated every couple weeks or month / this is just a static dump from a downloaded spreadsheet (unfortunately due to the private nature of data i can only get limited access). The column names should still match though.

    Appreciate any assistance you guys can give.

    Cheers,

    Pytheus
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,207

    Re: Index and Match with Wildcards

    Try this, copied down and across...
    =IFERROR(INDEX(Sheet2!$B$3:$E$500,MATCH("*"&$B4,Sheet2!$C$3:$C$500,0),MATCH(Sheet1!C$3,Sheet2!$B$3:$E$3,0)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-08-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2013/10/07/03
    Posts
    33

    Re: Index and Match with Wildcards

    Works great, thanks! I was so close to that though... Cant actually see what i was doing wrong.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,207

    Re: Index and Match with Wildcards

    Cant see what you had before, sorry If you show me, I may be able to see.

    Happy it worked for you, and thanks for the feedback

  5. #5
    Registered User
    Join Date
    11-08-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2013/10/07/03
    Posts
    33

    Re: Index and Match with Wildcards

    Nah just random forumlas i had tried earlier. What you wrote looks really simialr to what i had tried - probably had the right stuff in various combinations of the wrong formulas. Now just the effort of making the formula work with these excel files -.-v Got 3 different excel files from 3 different areas and i have to make them talk without making any changes if possible. This is why i prefer using Cognos - all this is already done for me lol.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,207

    Re: Index and Match with Wildcards

    OK well shout if you need more help

+ 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. [SOLVED] Make an Index/Match Sum Array with wildcards
    By TheAlchemyst in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-28-2014, 06:48 PM
  3. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Wildcards with functions INDEX and EQUIV
    By Fabrice in forum Excel General
    Replies: 4
    Last Post: 07-15-2005, 11:05 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