Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Lookup function to return a range of Cells
Hello
Im having a slight problem. I am looking to create a 'search engine' in excel that would automatically bring back a range of cells, from another worksheet.
For example. In cell A1 I would input a Beat Code such as D4550A. In Cell B1 I would then gather a list of staff who are working on that beat.
I have tried the v lookup function however this only returns one row of data, whereas I might be looking to return 10 or 15 rows based on who has got the beat code D4550A next to their name.
Is there any other method or adaptions to the vlookup formula that could return a range of cells, rather than just the one row?
Thanks in advance
Gareth.
RE: Lookup function to return a range of Cells
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
