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

1. ## 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. ## 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:
`Please Login or Register  to view this content.`

see attached:- Class schedule dates.xls

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

3. ## 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. ## 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. ## 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.``

6. ## 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. ## 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. ## 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?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.06553 seconds
• Memory Usage 9,058KB
• Queries Executed 15 (?)
Template Usage (33):
• (1)bbcode_code
• (1)footer
• (1)forumrules
• (1)gobutton
• (8)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)postbit_attachment
• (8)postbit_legacy
• (8)postbit_onlinestatus
• (8)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (48):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit
• tag_fetchbit_complete
• forumrules