create Lookup Function

[stevewilde]

Hi, have data concerning documents people have to write, which is updated weekly. Is there a way of looking up a name and then returning all the documents that they have to write. I have tried a vlookup, but this only returns the first entry and not the lot. Also i would prefer the document data for each person to be returned in to one cell. Is this possible? Thanks

[NBVC]

If you fill the empty cells in column A of the Raw Data sheet with relevant names, then you can use this UDF and apply it to get your concatenated lookups...

Add this code to a module in your workbook...

Please Login or Register  to view this content.

Then apply this formula in B2 of Results sheet

Please Login or Register  to view this content.

adjust ranges to suit your raw data and confirm it with CTRL+SHIFT+ENTER and not just ENTER and then copy it down.

[stevewilde]

What do you mean by add to a module - do you mean create a macro...forgive my lack of knowledge

[NBVC]

Yes...

Hit Alt+F11 for the VBE editor, go to Insert|Module and paste the code.. then on the sheet apply the formula.