Formula to retreive data into a sheet - Database vlookup or if analysis

I am having trouble with pulling up data from another sheet.

On the attached Excel file, I have 3tabs:

Master_List
H_Codes
Lab_match

In the lab_match tab, I want to be able to insert a group of chemicals in Columns A and B i.e. cas numbers and chemical name and be able to auto fill the rest of the Columns C to I. I have been able to use the Vlookup formula in C, D, E and F to obtain what I need there. The output for C, D, E and F comes from the Master_List Tab.

Problem: Depending on the H-Codes produced in Column C in the lab_match Tab (via Vlookup from Master List Tab), I would like to compare those multiple codes to the data in the H_Codes tab and provide an output into columns G, H and I.

So example C2 contains the code H301, then G2 should contain "Toxic if swallowed", H2 should contain "Acute toxicity, oral (chapter 3.1)" and I2 should contain "3". C2 also contains other H-codes e.g. H315, H318, H401 and the outputs of these should also be within G2.

Please help!
Report from Chemffx.xls

Originally Posted by FDibbins

There is no easy way to give you what you want, using regular formulas (maybe some-one can put some VBA together for you)

The way I would tackle this, would be to add a series of helper columns off to the side, to break out each of the codes, look up each of those, and then put the answers back together again.

If this something you think you can work with, let me know and we can start working on it?

Can we try your proposed method with helper columns? I have no knowledge of vba either and would need help for this.

This is great! Thank you! I will try to use it with the team..thanks again!!!