Multiple lookup values in a single cell

[rahanriad]

Hi experts.

I want to get multiple lookup values in a single cell with a comma separator. For instance column A has repetitive area name and B has Agents name under the area name. I want to get the value for all agents name under the same area.

I already solve the problem in excel 2016 with TEXTJOIN and IF array.

But in 2013 there is no TEXTJOIN formula. Is that possible with SUMPRODUCT??

Please help me. Please see the attached workbook for your clear understanding.

e.g

a = a1,a2,a3

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

You have a choice: either using a helper column, or using VBA. Which do you prefer?

[rahanriad]

Dear Glenn,

I prefer with a helper column. And one thing I tried with SUMPRODUCT.

Is it possible to do it by SUMPRODUCT.

[Glenn Kennedy]

Why the obsession with SUMPRODUCT??

In F4:
=IFERROR(E4&", "&INDEX(F5:F11,MATCH(D4,D5:D11,0)),E4)

copy down.

Then in I4, copied down:

=INDEX($F$4:$F$11,MATCH(H4,$D$4:$D$11,))

[rahanriad]

Why I am unable to download the workbook??

[Glenn Kennedy]

No idea. I will reattach it when I get back. In the meantime copy/paste the formulae ino your sample sheet, as described.

[rahanriad]

Thank you so much, Glenn. The solution is done but gone over the head.

[Glenn Kennedy]

It's quite clever. Not mine, however! I saw someone else use it here a few months ago and thought "that's neat!!".

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.