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>

 

[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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td]VisitDay[/td][td]Customer[/td][td][/td][td]

VisitDay​

[/td][td]

Count​

[/td][td]

Store(s)​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]MONDAY-1, MONDAY-3[/td][td]Store 1[/td][td][/td][td]

MONDAY-1​

[/td][td]

3​

[/td][td]Store 1[/td][td]Store 7[/td][td]Store 8[/td][/tr]
[tr][td]

3​

[/td][td]TUESDAY-2[/td][td]Store 2[/td][td][/td][td]

MONDAY-2​

[/td][td]

1​

[/td][td]Store 5[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]WEDNESDAY-1,SATURDAY-3[/td][td]Store 3[/td][td][/td][td]

MONDAY-3​

[/td][td]

2​

[/td][td]Store 1[/td][td]Store 8[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]THURSDAY-1, THURSDAY-2, THURSDAY-3[/td][td]Store 4[/td][td][/td][td]

MONDAY-4​

[/td][td]

2​

[/td][td]Store 5[/td][td]Store 7[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]MONDAY-2, MONDAY-4[/td][td]Store 5[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]TUESDAY-1, TUESDAY-2, TUESDAY-3[/td][td]Store 6[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]MONDAY-1, MONDAY-4[/td][td]Store 7[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]MONDAY-1, MONDAY-3[/td][td]Store 8[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]TUESDAY-2[/td][td]Store 9[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]WEDNESDAY-1,SATURDAY-3[/td][td]Store 10[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

VisitDay​

[/TD]
[TD]

Customer​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Count​

[/TD]
[TD]

VisitDay​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

MONDAY-1, MONDAY-3​

[/TD]
[TD]

Store 1​

[/TD]
[TD][/TD]
[TD]

MONDAY-1​

[/TD]
[TD]

3​

[/TD]
[TD]

Store 1​

[/TD]
[TD]

Store 7​

[/TD]
[TD]

Store 8​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

TUESDAY-2​

[/TD]
[TD]

Store 2​

[/TD]
[TD][/TD]
[TD]

MONDAY-2​

[/TD]
[TD]

1​

[/TD]
[TD]

Store 5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

WEDNESDAY-1,SATURDAY-3​

[/TD]
[TD]

Store 3​

[/TD]
[TD][/TD]
[TD]

MONDAY-3​

[/TD]
[TD]

2​

[/TD]
[TD]

Store 1​

[/TD]
[TD]

Store 8​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

THURSDAY-1, THURSDAY-2, THURSDAY-3​

[/TD]
[TD]

Store 4​

[/TD]
[TD][/TD]
[TD]

MONDAY-4​

[/TD]
[TD]

2​

[/TD]
[TD]

Store 5​

[/TD]
[TD]

Store 7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

MONDAY-2, MONDAY-4​

[/TD]
[TD]

Store 5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

TUESDAY-1, TUESDAY-2, TUESDAY-3​

[/TD]
[TD]

Store 6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

MONDAY-1, MONDAY-4​

[/TD]
[TD]

Store 7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

MONDAY-1, MONDAY-3​

[/TD]
[TD]

Store 8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

TUESDAY-2​

[/TD]
[TD]

Store 9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

WEDNESDAY-1,SATURDAY-3​

[/TD]
[TD]

Store 10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.