+ Reply to Thread
Results 1 to 8 of 8

INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

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

    INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    I have uploaded the workbook I am working on. Class schedule dates.xls- It has my schedule of classes on the left. On the right I showed the results I want for the first week they potentially can fill Column K-Column N.
    In K8 is a formula that gets one good result but I need to MATCH the week (W) of the class in two ways - "*W3*" or "*,3" , and I need to change it to an array formula that gives me the second match in column L, the third match in column M, and the fourth match in column N. Maybe INDEX isn't the best function to use in this situation?

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    HI bkwins,

    Welcome to the forum.

    Not sure if you are considering the weeks appearing in column H, but try using below formula:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached:- Class schedule dates.xls


    Regards,
    DILIPandey
    <click on below * if this helps>
    Attached Files Attached Files
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

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

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    Thanks so much for your quick response! But the cells you filled in with the formula should only have found one match because on Monday there is only one class on the 2nd week - this one "2012Clin. Med9-10Class W2,5,9,11" The "W" stands for "week" and the numbers following it tell which week of the semester the class is on.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    Okay.. can you manually fill in your expected results and I will try and update the formula? thx





    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,191

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    pL SEE THE ATTACHED FILE WITH aRRAY FORMULA.
    FORMULA USED in k3

    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    Kvsrinivasamurthy, that formula works beautifully!!! Thank you! Would it be too much trouble to ask what it does in plain English? Also what would it look like if you weren't trying to match multiple substrings ie "*W3*" or "*,3"? Thanks!

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,191

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    Thanks for the compliment.
    By matching the day in J column with 1st Row the column is selected (for wednesday columnC).The week number in H column is searched in all cells of selected column.The cell values containing that week number is selected serially.*W* cannnot be used because week number coming in between will no have W before number. ,3 cannot be used because when week number comes in begining it will be W3 not W,3.I hope I am clear.

    Thanks once again.

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

    Re: INDEX MATCH formula that matches "*W3" or "*,3" and displays multiple matches in array

    What can I add to the formula to keep the #VALUE error from showing?

+ 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