INDEX MATCH array formula that matches substring n gives multiple matches

**bkwins** · 04-02-2013, 03:51 AM

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

**bebo021999** · 04-02-2013, 06:44 AM

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))))

**benishiryo** · 04-02-2013, 06:52 AM

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

**daddylonglegs** · 04-02-2013, 07:44 AM

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

**bkwins** · 04-12-2013, 04:57 AM

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!