search multiple criteria and return multiple values within the same cell

This will either be horrible or will require VBA in your excel version. Please confirm your current version & amned your user profile, as needed.

Your calculation column is also full of links to external files. That's no use to us. The sample should be 10-20 rows and complete in itself. Please amend and reattach with some manually calculated expected results.

So, I went ahead and assumed that you're still using an older Excel version and I set up a macro-based solution. Enable macros on opening.

Take a look and see if it works for you. Then I can explain/.alter depending on your Excel version.

Advice: never set up a table to include the entire column, unless you do have 1000,000+++ rows of data. the table will auto-extend as you add data.


These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

***IF*** you are now using Office 365, this is much, much easier.

Can it be achieved without macros
With company security and that, it might be better without macro
I was able to the match and find the entries using index small, match, couldn't get the job nos in the same cell though

Can you please assist

Are you confirming that you are still using Excel 2010? If not, what? This is important, so we need an answer.

Yes... with O365, otherwise, no. If you're not using O365, you'd need a lot of helper columns.

No response...

This works for O365.

=TEXTJOIN(", ",,FILTER(Table1[[Job no]:[Job no]],(Table1[[Task]:[Task]]=test!E$2)*(Table1[[date]:[date]]=test!$B7),""))

If you can't use VBA and don't have this version.. can you define the MAXIMUM number of jobs in the ONE cell.

1. You sent me a PM saying that you were using O365. Here are instructions on ghow to update your profile, so Qs here can be answered as efficiently as possible.

2. Please check out the solution offered at Post #8, which should be fine in your Excel version.