Lookup function to return a range of Cells | Excel forum

Hi Gareth, thanks for your query. You could achieve this using an advanced filter and specifying the criteria range, but the results would be outputted to individual cells. You said "In Cell B1 I would then gather a list of staff who are working on that beat". Do you intend to concatenate the results and output them to B1? If so, be aware the Concatenate function does not accept range references, and you'll have to use the "&" operator to link each result. As such you will have to cycle through the results from the filter using a loop, which therefore means you'll need to use VBA. A macro to achieve what you want shouldn't be too difficult, just take the contents of cell A1, use it to filter out the data from the table, then select the resulting range, concatenate it (either looping through the range and using "&" or using a bespoke Function to do so) and output it to cell B1. Fiddly, but not impossible.

Hope this helps,

Anthony