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.

VisitDay	Customer
MONDAY-1, MONDAY-3	Store 1
TUESDAY-2	Store 2
WEDNESDAY-1,SATURDAY-3	Store 3
THURSDAY-1, THURSDAY-2, THURSDAY-3	Store 4
MONDAY-2, MONDAY-4	Store 5
TUESDAY-1, TUESDAY-2, TUESDAY-3	Store 6
MONDAY-1, MONDAY-4	Store 7
MONDAY-1, MONDAY-3	Store 8
TUESDAY-2	Store 9
WEDNESDAY-1,SATURDAY-3	Store 10

<colgroup><col><col></colgroup><tbody>
</tbody>

<strike></strike>

and I'm trying to get this value

	Count	VisitDay
MONDAY-1	3	Store 1	Store 7	Store 8
MONDAY-2	1	Store 5
MONDAY-3	2	Store 1	Store 7
MONDAY-4	2	Store 5	Store 7

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

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