Need help! Array Forumla to provide multiple occurences of same vlookup!?

I have a sheet of data which I would like to format to make easily accessible and searchable.

Column A contains the names of employees, and in Column B adjacent to their name is a list of safety memos that they have signed-off. I copied their name down the column next to each memo so that each B value has a corresponding A value directly adjacent (their name repeats next to each memo they've signed until the next name begins.)

Now, I created a separate sheet as a "homepage" which I would like to use to search and filter the data. I made a small dropdown list of each name that occurs in Column A. This is in cell B4 on the sheet. So for whichever name is selected in B4 I would like to display a list of the memos they have completed next to it starting in C4 and expanding down the column.

Not too complicated I would think, right? Well I tried a Vlookup function which I am firmiliar with, but that would only display the very first occurance. I realized at this point that I would need to use an Array formula, which I am not firmiliar with. I did a little google search and found some seemingly helpful links however after many attempts of trial and error I am still stuck.

Here is what I have so far:

=INDEX('Cooks Training'!A1:A2927, SMALL(IF($B$4='Cooks Training'!A1:A2927,ROW('Cooks Training'!A1:A2927)),ROW(1:1)),2)

which returns a reference error.

'Cooks Training' is the data sheet. Cells A1:A2927 contain the names (repeating) of each employee on this sheet, and Cells B1:B2927 contain the names of the memos of which they have completed (with a couple blanks in this column as well.)

Please help me guys, I would love to be able to just select a name from the dropdown list in Cell B4 and have a list of their memos displayed starting in cell C4 and expanding down.

Training & Sign-offs2.xls

To be able to see a list of names as you describe will need a macro in the SheetChange event. (VLookups only return a single value).

But have you considered a Pivot Table, or failing that just simply using an Autofilter on your data?

Hi,

Here's the Pivot table option which I mentioned. You refer to VLOOKUPS in your post but I'm not sure why since as I said they only return one value, but in any case I couldn't see any in your file. Just select a name from B1 which is an inherent part of the Pivot Table.

you could try this array formula in C4 of the Homepage Index sheet :

Formula:

Please Login or Register  to view this content.

Enter With Cntrl+Shft+Enter
drag down

Training & Sign-offs2.sol2.xls

Hope this helps

Jdubs, you should start a new thread, if you think this thread is particularly relevant, provide a link to it

Meanwhile, I'll have a look at it