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>

 

[Special-K99]

Re: Need Help [Vlookup multiple values with comma separated list]

Output is wrong

MONDAY-3 should be Stores 1 and 8

Interesting problem, thinking...

 

[Special-K99]

Re: Need Help [Vlookup multiple values with comma separated list]

The usual way to produce a small list fronm a larger one (the most common request is to remove blank cells from a column) is to use an array formula like this

'{=IFERROR(INDEX(column range,SMALL(IF((column range<>""),ROW(column range)),ROW(A1))-(ROW(first cell)-1),1),"")}

however you have multiple value in column A separated by a comma so that makes it complicated

Still thinking...

 

[snjpverma]

Re: Need Help [Vlookup multiple values with comma separated list]

separate the weekdays by using "Text to Column" option and then do a pivot. It will be the most easiest method

 

[Special-K99]

You have MONDAY-1 and you have MONDAY-4
How many is this likely to go up to?

 

[snjpverma]

k99, I don't think any month will have more than 5 days. but let's wait for OP's answer.

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	VisitDay	Customer		VisitDay​	Count​	Store(s)​	​	​
2​	MONDAY-1, MONDAY-3	Store 1		MONDAY-1​	3​	Store 1	Store 7	Store 8
3​	TUESDAY-2	Store 2		MONDAY-2​	1​	Store 5
4​	WEDNESDAY-1,SATURDAY-3	Store 3		MONDAY-3​	2​	Store 1	Store 8
5​	THURSDAY-1, THURSDAY-2, THURSDAY-3	Store 4		MONDAY-4​	2​	Store 5	Store 7
6​	MONDAY-2, MONDAY-4	Store 5
7​	TUESDAY-1, TUESDAY-2, TUESDAY-3	Store 6
8​	MONDAY-1, MONDAY-4	Store 7
9​	MONDAY-1, MONDAY-3	Store 8
10​	TUESDAY-2	Store 9
11​	WEDNESDAY-1,SATURDAY-3	Store 10

In E2 just enter and copy down:

=SUMPRODUCT(--ISNUMBER(SEARCH(","&D2&",",","&SUBSTITUTE($A$2:$A$11," ","")&",")))

In F2 control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($F2:F2)>$E2,"",INDEX($B$2:$B$11,SMALL(IF(ISNUMBER(SEARCH(","&$D2&",",","&SUBSTITUTE($A$2:$A$11," ","")&",")),ROW($B$2:$B$11)-ROW($B$2)+1),COLUMNS($F2:F2))))

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	VisitDay​	Customer​			Count​	VisitDay​
2​	MONDAY-1, MONDAY-3​	Store 1​		MONDAY-1​	3​	Store 1​	Store 7​	Store 8​
3​	TUESDAY-2​	Store 2​		MONDAY-2​	1​	Store 5​
4​	WEDNESDAY-1,SATURDAY-3​	Store 3​		MONDAY-3​	2​	Store 1​	Store 8​
5​	THURSDAY-1, THURSDAY-2, THURSDAY-3​	Store 4​		MONDAY-4​	2​	Store 5​	Store 7​
6​	MONDAY-2, MONDAY-4​	Store 5​
7​	TUESDAY-1, TUESDAY-2, TUESDAY-3​	Store 6​
8​	MONDAY-1, MONDAY-4​	Store 7​
9​	MONDAY-1, MONDAY-3​	Store 8​
10​	TUESDAY-2​	Store 9​
11​	WEDNESDAY-1,SATURDAY-3​	Store 10​
12​

<tbody>
</tbody>

Formula in E2 copied down
=COUNTIF(A:A,"*"&D2&"*")

Array formula in F2 copied across and down
=IF($E2>=COLUMNS($F2:F2),INDEX($B$2:$B$11,SMALL(IF(ISNUMBER(SEARCH($D2,$A$2:$A$11)),ROW($B$2:$B$11)-ROW($B$2)+1),COLUMNS($F2:F2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[andersen_yunan]

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

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.