Search against a range of criteria and display whole rows as results

Hi,

I have a chemical inventory with lots of data, about 10,000 rows, in Sheet 1. I need to search within column B of the inventory in Sheet 1 against several chemicals listed in Sheet 2. If there are matches, the results will display the whole row of the data. The results will be displayed in Sheet 3.

Please refer to attached file for a sample inventory.

For example, in sheet 2, the first chemical is "butanol". And in Sheet 1 there is only 1 row (row 10) with this data in Column B. Therefore the information in row 10 will be displayed in Sheet 3.

The second chemical in sheet 2 will be "ethanol" and there are 5 rows with this match. So information in rows 22 to 26 will be displayed in Sheet 3 below the results for "butanol". The results of the third, fourth, and subsequent chemicals in Sheet 2 are then displayed accordingly below.

In summary, I have a list of chemicals in Sheet 2 to search against the information in Sheet 1 Column B and the results will be displayed in Sheet 3. Is there a way to do this in Excel 2010?

Other ways to display the results are also welcome.

Thanks!

If you want a macro solution, let me know.

Originally Posted by Melvinrobb

Will this work?

Thanks a lot! This works!

Originally Posted by AB33

If you want a macro solution, let me know.

Thanks for the offer! The solution by Melvinrobb works!