Require Function to Lookup and Concatenate Mulltiple Results To Single Cell

Hi All,

I'm currently working on a spreadsheet where I need to look up and list single/multiple results and return the concatenated results in a single cell against each look-up value.
What I am after is pretty much something like a INDEX(Reference_array,MATCH(Lookup_value,Lookup_array,0)) type formula but with multiple outputs per look up value.

Ideally I think this could be solved in one step using a UDF where reference value and look-up value ranges and look up value are inputs and output is respective reference value(s) for each look-up value.

This may not be the easiest description of the problem to follow so I'll attach a simplified example but having problem uploading file at the moment.

Please let me know if you have any questions and solution suggestions on how to get the results I am after.

Thanks.
Nutty.

Hi nilem,

First of all, thank you for your reply and really helpful answer!
This seems to work really well if the look-up range and reference range are in columns right beside each other.

One question is how would this work if the "Names" --> look-up range and "Fruit Eaten" --> reference range are in columns which are not immediately beside each other e.g. "Names" are in column C and "Fruit Eaten" in column F? (Note: both look-up and reference columns should always contain the same number of elements).

I think if the above can be solved then it would be perfect!

Nutty