Looking for a formula that will return multiple results from a lookup separated by a comma

Hi,

I have a 3-sheet workbook where I need a cell in sheet 3 to look at the data in Sheet 1 or Sheet 2, and return all instances of the lookup into 1 cell separated by a comma in Sheet 3.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Would you also explain the overall process. By that I dpn;t mean tell us wat you're exsiting formulae mean, but tell us what you start with and how you process it to arrive at the results you show.

I haven't the faintest idea what you want. Please add some manually calculated answers.

OK. Clear!! One more Q. Can there be MORE THAN 1 match per sheet (thus more than two in total)? If more than1 per sheet, have you any idea how many (roughly)?

This will need VBA. Is that OK?

With 34.... there are NO other options.


You didn't say which columns I should be looking at in in sheet 2... so I guessed.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then use this array formula in A3:

=ConcatAll(IF('Sheet 1'!$A$2:$A$79=A3,'Sheet 1'!$B$2:$B$79,""),", ")&", "&ConcatAll(IF('Sheet 2'!$E$2:$E$79=A3,'Sheet 2'!$H$2:$H$79,""),", ")

Array Formulae 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 curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - 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...

Any reason why column I is highlighted in yellow?

OK!! I just thought I might have missed something.... again.

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.

Gimme 5...

Sheet 1 Rows 4 and 62 and Sheet 2 row 9.... 3 in total.

I misread your request. I thought you wanted me to look at BOTH sheets. So, amend the array formula to:

=ConcatAll(IF('Sheet 1'!$A$2:$A$79=A3,'Sheet 1'!$B$2:$B$79,""),", ")

and it looks only at Sheet 1.

---

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.