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

[bkwins]

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?

[dilipandey]

HI bkwins,

Welcome to the forum.

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

Please Login or Register  to view this content.

see attached:- Class schedule dates.xls


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

[bkwins]

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.

[dilipandey]

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>

[kvsrinivasamurthy]

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

Please Login or Register  to view this content.

[bkwins]

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!

[kvsrinivasamurthy]

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.

[bkwins]

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