Vlookup multiple values with comma separated list

[andersen_yunan]

I'm looking to use Excel to look up and return multiple reference values for a given key. VLookup does something very similar to what I need - but only returns a single match. The thing is, my reference cell contains comma separated list. For example, here is the example of my reference data.

[TABLE="width: 322"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]VisitDay[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD]MONDAY-1, MONDAY-3[/TD]
[TD]Store 1[/TD]
[/TR]
[TR]
[TD]TUESDAY-2[/TD]
[TD]Store 2[/TD]
[/TR]
[TR]
[TD]WEDNESDAY-1,SATURDAY-3[/TD]
[TD]Store 3[/TD]
[/TR]
[TR]
[TD]THURSDAY-1, THURSDAY-2, THURSDAY-3[/TD]
[TD]Store 4[/TD]
[/TR]
[TR]
[TD]MONDAY-2, MONDAY-4[/TD]
[TD]Store 5[/TD]
[/TR]
[TR]
[TD]TUESDAY-1, TUESDAY-2, TUESDAY-3[/TD]
[TD]Store 6[/TD]
[/TR]
[TR]
[TD]MONDAY-1, MONDAY-4[/TD]
[TD]Store 7[/TD]
[/TR]
[TR]
[TD]MONDAY-1, MONDAY-3[/TD]
[TD]Store 8[/TD]
[/TR]
[TR]
[TD]TUESDAY-2[/TD]
[TD]Store 9[/TD]
[/TR]
[TR]
[TD]WEDNESDAY-1,SATURDAY-3[/TD]
[TD]Store 10[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

and I'm trying to get this value

[TABLE="width: 354"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]Count[/TD]
[TD="align: center"]VisitDay[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]MONDAY-1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Store 1[/TD]
[TD="align: center"]Store 7[/TD]
[TD="align: center"]Store 8[/TD]
[/TR]
[TR]
[TD="align: center"]MONDAY-2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Store 5[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]MONDAY-3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Store 1[/TD]
[TD="align: center"]Store 7[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]MONDAY-4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Store 5[/TD]
[TD="align: center"]Store 7[/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
Any help as to how to approach handling multiple values in this context is apprecited. Thanks.<strike></strike>

 

[Aladin Akyurek]

Thanks a lot guys! it works perfectly!
yes, it should be store 1 and 8.. my bad
there are no working day for week 5, k99

You are welcome. If days are numbered consecutively, the set up of #7 won't confound MONDAY-1 and MONDAY10.