How to return multiple values to a single cell based on multiple criteria

**lwallace** · 05-11-2012, 10:38 AM

Hello All,

I have been searching this forum for a solution to my problem for a few days now for. While I've learned some new tricks, I haven't been able to find a solution that meets all of my needs and I'm hoping someone can help. I've attached a sample workbook.

The information that appears on Sheet2 will actually be contained in a separate file. Sheet1 is a file the client currently uses and populates manually, but I'm trying to get it to populate automatically from Sheet2. I need to return all the days (column C) from Sheet2 that match the Name (A) and Month (B) and populate them in a single cell in Sheet1.

That is, cell B3 in Sheet1 would display "3,4,5" based on B1 (name) and A2 (month) matching A3:A5 and B3:B5 of Sheet2 respectively. I've found a few examples of INDEX that returns the proper dates, but haven't been able to get all of those days into a single cell.

I hope this makes sense! Thanks in advance.

**tigeravatar** · 05-11-2012, 11:04 AM

lwallace,

Welcome to the forum!
Unfortunately, the CONCATENATE function doesn't work with arrays, so the only viable solution is going to be VBA or an add-in. I recommend using the MoreFunc addin which contains the MCONCAT function which should be able to fit your needs.

**lwallace** · 05-11-2012, 11:13 AM

Thanks for this. I'm not familiar with VBA or add-ins (my skill level is quite basic in comparison to what is found on this forum); would my client have to download the add-in as well in order for it to work for them? If so, would you know how I could accomplish this with VBA?

Thanks again.

**tigeravatar** · 05-11-2012, 11:44 AM

Yes, they would have to download the add-in as well. With a VBA solution, they will have to enable macros or the UDF (User Defined Function) won't work.

Attached is a modified version of your example workbook. Please note that it is now a .xlsm file which means it contains VBA code. When you open it, you will have to enable macros.

In Sheet1 cell B3 is this UDF formula:

Please Login or Register  to view this content.

In cell B4 is this formula (not a UDF, it is a native Excel function in Excel 2007+):

Please Login or Register  to view this content.

This is the code used for the UDF:

Please Login or Register  to view this content.

How to use a UDF (User Defined Function):

Make a copy of the workbook the UDF will be tested on
- Always run new code and UDFs on a workbook copy, just in case the code doesn't run smoothly
- This is especially true of any code that deletes anything
In the copied workbook, press ALT+F11 to open the Visual Basic Editor
Insert | Module
Copy the provided code and paste into the module
Close the Visual Basic Editor
In the desired cell, call the UDF like you would a regular formula: =UDFNAME(Argument1, Argument2, etc)
I named this one ConcatIfs

**lwallace** · 05-11-2012, 03:23 PM

That seems to work perfectly. Thank you so much!!

**wedwards123** · 08-20-2014, 06:32 AM

long shot as so long after the origianl post...... i've copied your data into my own spreadsheet and then again with formulas and UDF.... but i just get blank cells on sheet1?

the end product i'm after (i'm sure is the same as yours) is i have a spreadsheet1 that has names, skills, Age and department these could be multiples ie
Name J Smith Skill 1 Dept A Age 25
Name B Smith Skill 1 Dept A Age 30
Name J Smith Skill 4 Dept B Age 25

I need a formula or UDF or VBA that would return Name and Age based on the criteria of Dept and Skill. For example:
Dept A Skill 1 would return IN ONE CELL : J Smith 25, B Smith 30

Thanks in advance!