Match/Index with multiple entries

Ok, I have a workbook that I use to enter in reviews my supervisors do on other employees. This information is contained on the worksheet called "Deal" and it has rows A through Y. The information starts on row 9. Each month is divided by a few rows and each month is also grouped so I can collapse them when I am not entering in the information or expand them when I want to see something from a previous month.

I have another worksheet called "all per auditor". On this sheet, I want to be able to pull up all of the reviews an employee did in each month and the information on them. I understand I can use data/sort to show all of that however, my rows are not all the same size... some are merged, others are not, and some are hidden (not sure if that matters). Also, I want it to automatically populate the results so that others that are not excel or computer inclined will easily be able to view the information.

I know I can use VLookup however, the information I want it to look up is to the left of the supervisor's name. I also know I can use match/index to pull the information from the left however, the supervisor's name appears more than once and I want it to pull all of the reviews, not just the first one.

Lastly, I could not upload the whole file as it was too large however, I tried to duplicate a few of the pages on this file I uploaded. I hope it works ok.

Originally Posted by mrice

This might help if you are in a rush.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.


Add a new sheet at the end so that you have 4 in total. Go to the deals sheet. Select an example of the name that you are interested in and run the macro ( Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.) This will produce a listing of all their activities on sheet 4.

Thanks for the assistance. I selected the supervisor's name on the Deal sheet and ran the macro... however, it just returned everything in row 1... instead of the reviews they conducted. I also tried selecting their name in the settings tab but, that didn't work either.

Ok, I see it working now however, it is pulling up everything on an employee (anytime they are mentioned in the B column). What if I wanted it to pull up anytime they are named in the X column?