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?
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.
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!
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.
Bookmarks