Combine text cells using common id numbers

[Gain]

Hi,

I have 15 spreadsheets, each having a column of id numbers for chemicals and a column of corresponding chemical names. Each file is a list of chemicals that cause one condition. Each file is a list of chemicals that are either recognized or suspected of causing a certain health condition. For example, one file contains a list of chemicals that are recognized as being carcinogenic (causing cancer), and has one column of chemical names and the next column with the corresponding chemical id numbers. Another example: One file contains a list of chemicals that are suspected of causing developmental issues, with one column of chemical names and corresponding id numbers.

I need to make one master spreadsheet with all of the chemicals listed with all of the effects that they are either recognized of causing or are suspected of causing, in the following format:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]CHEMICAL NAME[/TD]
[TD]HAZARD[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Benzene[/TD]
[TD]Cancer, developmental, Respiratory[/TD]
[TD]recognized[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Benzene[/TD]
[TD]GI tract, neurotoxic[/TD]
[TD]suspected[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Arsenic[/TD]
[TD]Reproductive[/TD]
[TD]recognized[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Arsenic[/TD]
[TD][/TD]
[TD]suspected[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fluorene[/TD]
[TD][/TD]
[TD]recognized[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fluorene[/TD]
[TD]Skin or sense organ, musculoskeletal[/TD]
[TD]suspected[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated,

Thank you in advance

 

[narendra]

You are missing the Column# reference in the formula that comes before the last parameter ",0".

 

[Gain]

Changing the function to

=vlookup (CAS Registry Number (or EDF Substance ID), '[Recognized Reproductive Toxicity.csv]Recognized Reproductive Toxicit'!$B:$B,2,FALSE)

, I still get an error message