+ Reply to Thread
Results 1 to 5 of 5

INDEX MATCH array formula that matches substring n gives multiple matches

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

    INDEX MATCH array formula that matches substring n gives multiple matches

    I have a several column range that shows classes matched up in the same row with the date the class is held. In another column I have a straight list of the class names that I then want to show the dates that each class has class in the adjacent four columns. Also the class name cannot be matched exactly because the class names in the range have variations in the text that follows the class name. The attached workbook shows a formula that works in Sheet 1 for almost the identical situation but didn't work in Sheet 2 even when matching the whole class name as given in the adjacent cell.
    IndexMatchArrayfromRange.xls

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,506

    Re: INDEX MATCH array formula that matches substring n gives multiple matches

    Try this:
    =(INDEX($C$3:$C$92,SMALL(IF(ISNUMBER(SEARCH("Clin Med1-2",$E$3:$K$92)),ROW($E$3:$K$92)-MIN(ROW($E$3:$K$92))+1,""),COLUMN(A1))))

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

    Re: INDEX MATCH array formula that matches substring n gives multiple matches

    hi bkwins. would be good if you can give the desired results next time. that way, we know we're in the right direction. my guess from your formula in Sheet2(2)!N3 is:
    =(INDEX($C$3:$C$92,SMALL(IF(ISNUMBER(SEARCH("Clin Med1-2",$E$3:$K$92)),ROW($E$3:$K$92)-MIN(ROW($E$3:$K$92))+1,""),COLUMN(A1))))

    you can't use the wildcards method (*) in this scenario. basically, i use the search to find the substring in those cells. if it finds them, it will return the position the substring is at. otherwise, i'll have a VALUE error. so i used ISNUMBER to counter that. hence, whatever is found will be TRUE & those not will be FALSE. whenever it's TRUE, it will then use the ROW formula you have.

    this will then answer your question. ROW($E$3:$K$92) will return:
    {3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22... all the way to .... 90;91;92}

    if i want to return the 1st value, it's 1 & not 3. so in here, the formula you have uses the MIN(ROW($E$3:$K$92)) to subtract the smallest row of the range. the smallest is 3, so if you take the above range minus 3 to all values, you'll get:
    {0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22... all the way to... 87;88;89}

    the last step is then to add a +1 to it so that it will read as:
    {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22... all the way to... 88;89;90}

    hope that clears things up

    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

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: INDEX MATCH array formula that matches substring n gives multiple matches

    Rather than "hardcoding" the search value in the formula (which means you manually need to change it on every row) you can use this version in N3

    =LOOKUP(10^7,IF({1,0},0,SMALL(IF(LEFT($E$3:$K$92,FIND(CHAR(10),$M3))=LEFT($M3,FIND(CHAR(10),$M3)),$C$3:$C$92),COLUMNS($N3:N3))))

    confirm with CTRL+SHIFT+ENTER and copy across and down - if there are less that 4 dates you get zero values, custom format as mm/dd;; to show these as blanks - see attached
    Attached Files Attached Files
    Audere est facere

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

    Re: INDEX MATCH array formula that matches substring n gives multiple matches

    Sorry for the delayed response especially since you all replied so quickly. This is a long term project of mine and I have been super busy with other stuff lately.
    So, bebo and beni I tried that formula and it works great! Thanks!
    daddylonglegs, I opened the file you uploaded and it worked fine but then when I plugged it into my real document it just leaves all the cells blank. My original has Chinese characters so I changed it but I guess it makes a difference. I have uploaded the original file again for you to look at. OriginalIndexMatchArrayfromRange.xlsThanks to all of you!
    Last edited by bkwins; 04-12-2013 at 05:03 AM.

+ 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