search data us from column 1 from several worksheets and list results to another worksheet

[johnkay]

Hi, trying to figure this out but not getting much success..
I have a list of names in 10 worksheets in column one (Class 1, Class 2, Class 3 ...etc). I need to find all the names that matches based on my search criteria (ie. "M*) and list the results to another worksheet (My List) and if no match found than return black. I've attached a sample excel file as a reference. I guess I need an array formula in "My List" worksheet? Any help is appreciated. Thanks. Right now I'm using Filter to search then copy paste to "My List" but takes longer because I have to do that on each work sheet so looking for a formula as an alternative.

[alansidman]

VBA solution

Please Login or Register  to view this content.

[johnkay]

Thanks a lot. This worked like a charm. Any way to achieve this using functions like index, match,vlookup, countif ? just want to learn them.. not to strong in writing VBA codes.

[protonLeah]

Here's one solution. Create named ranges corresponding to the used range on each tab, i.e., Class_1, etc. Change the column heading on My List to match those names.
In A2 use:

Please Login or Register  to view this content.

copied down and across.