Hi
I have several thousand lines of chemical data as shown in columns A-E below. I am trying to sort this into some chemical groupings using specific text (part of the entry) in Column D.
I am using the following formula in Column F to search the entries in Column D, and then list the name from Column A (in Column F) if the wanted search terms are found.
My difficulty is that I want to exclude certain search terms, but I cant work out how to add this into my formula:
=IF((SEARCH({"36","38","36/38"},D3)),A3,"-")
The search terms I want to include are: "36", "38", "36/38"
The search terms I want to exclude are: "37", "36/37", "37/38", "36/37/38"
I would be very grateful for any advice.
Georgia
[TABLE="width: 1311"]
<tbody>[TR]
[TD]Col A - Substance
[/TD]
[TD]Col B - CAS number
[/TD]
[TD]Col C - Symbol
[/TD]
[TD]Col D - Risk-phrases
[/TD]
[TD]Col E - Safety phrases
[/TD]
[TD]Col F - Group A items identified
[/TD]
[TD]LInes that should excluded
[/TD]
[/TR]
[TR]
[TD]ACEPHATE
[/TD]
[TD]30560-19-1
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2)-36
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETAL
[/TD]
[TD]105-57-7
[/TD]
[TD]F,Xi
[/TD]
[TD]11-36/38
[/TD]
[TD](2-)9-16-33
[/TD]
[TD]ACETAL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETALDEHYDE
[/TD]
[TD]75-07-0
[/TD]
[TD]F+,Xn
[/TD]
[TD]12-36/37-40
[/TD]
[TD](2-)16-33-36/37
[/TD]
[TD]ACETALDEHYDE
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]ACETAMIDE
[/TD]
[TD]60-35-5
[/TD]
[TD]Xn
[/TD]
[TD]40
[/TD]
[TD](2-)36/37
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc.>90%
[/TD]
[TD]64-19-7
[/TD]
[TD]C
[/TD]
[TD]Oct-35
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc. 25%-90%
[/TD]
[TD][/TD]
[TD]C
[/TD]
[TD]34
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ANHYDRIDE
[/TD]
[TD]108-24-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE
[/TD]
[TD]67-64-1
[/TD]
[TD]F
[/TD]
[TD]11
[/TD]
[TD](2-)9-16-23-33
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE CYANOHYDRIN
[/TD]
[TD]75-86-5
[/TD]
[TD]T+,N
[/TD]
[TD]26/27/28-50
[/TD]
[TD](1/2-)7/9-27-45-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONITRILE
[/TD]
[TD]75-05-8
[/TD]
[TD]F,T
[/TD]
[TD]11-23/24/25
[/TD]
[TD](1/2-)16-27-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETOPHENONE
[/TD]
[TD]98-86-2
[/TD]
[TD]Xn
[/TD]
[TD]22-36
[/TD]
[TD](2-)26
[/TD]
[TD]ACETOPHENONE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLACETONE
[/TD]
[TD]123-54-3
[/TD]
[TD]Xn
[/TD]
[TD]Oct-22
[/TD]
[TD](2-)21-23-24/25
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYL CHLORIDE
[/TD]
[TD]75-36-5
[/TD]
[TD]F,C
[/TD]
[TD]11-14-34
[/TD]
[TD](1/2-)9-16-26-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLENE
[/TD]
[TD]74-86-2
[/TD]
[TD]F+
[/TD]
[TD]5/06/2012
[/TD]
[TD](2-)9-16-33
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-ACETYL-6-METHYL-2H-PYRAN-2,4(3H)-DIONE
[/TD]
[TD]520-45-6
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACLONIFEN
[/TD]
[TD]74070-46-5
[/TD]
[TD]N
[/TD]
[TD]50/53
[/TD]
[TD]60-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE
[/TD]
[TD]302-27-2
[/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE, SALTS
[/TD]
[TD][/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45 Note A
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACROLEIN
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLALDEHYDE
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLAMIDE
[/TD]
[TD]79-06-1
[/TD]
[TD]T
[/TD]
[TD]45-46-24/25-48/23/24/25
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD][/TD]
[TD]Xi
[/TD]
[TD]36/37/38
[/TD]
[TD](2-)26-28
[/TD]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]specified elsewhere in this Annex, conc.³10%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLIC ACID
[/TD]
[TD]79-10-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-36-45 Note D
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLONITRILE
[/TD]
[TD]107-13-1
[/TD]
[TD]F,T
[/TD]
[TD]45-11-23/24/25-38
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADIPIC ACID
[/TD]
[TD]124-04-9
[/TD]
[TD]Xi
[/TD]
[TD]36
[/TD]
[TD="align: right"]-2
[/TD]
[TD]ADIPIC ACID
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADZN
[/TD]
[TD]78-67-1
[/TD]
[TD]E,Xn
[/TD]
[TD]2-11-20/22
[/TD]
[TD](2-)39-41-47
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALACHLOR
[/TD]
[TD]15972-60-8
[/TD]
[TD]Xn
[/TD]
[TD]22-40-43
[/TD]
[TD](2-)36/37/39
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDICARB
[/TD]
[TD]116-06-3
[/TD]
[TD]T+
[/TD]
[TD]27/28
[/TD]
[TD](1/2-)22-36/37-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDRIN
[/TD]
[TD]309-00-2
[/TD]
[TD]T,N
[/TD]
[TD]24/25-40-48/24/25-50/53
[/TD]
[TD](1/2-)22-36/37-45-60-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLETHRIN
[/TD]
[TD]584-79-2
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2-)36
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLIDOCHLOR
[/TD]
[TD]93-71-0
[/TD]
[TD]Xn
[/TD]
[TD]21/22-36/38
[/TD]
[TD](2-)26-28-36/37/39
[/TD]
[TD]ALLIDOCHLOR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLYL ALCOHOL
[/TD]
[TD]107-18-6
[/TD]
[TD]T,N
[/TD]
[TD]10-23/24/25-36/37/38-50
[/TD]
[TD](1/2-)36/37/39-38-45-61
[/TD]
[TD]ALLYL ALCOHOL
[/TD]
[TD]Should exclude
[/TD]
[/TR]
</tbody>[/TABLE]
I have several thousand lines of chemical data as shown in columns A-E below. I am trying to sort this into some chemical groupings using specific text (part of the entry) in Column D.
I am using the following formula in Column F to search the entries in Column D, and then list the name from Column A (in Column F) if the wanted search terms are found.
My difficulty is that I want to exclude certain search terms, but I cant work out how to add this into my formula:
=IF((SEARCH({"36","38","36/38"},D3)),A3,"-")
The search terms I want to include are: "36", "38", "36/38"
The search terms I want to exclude are: "37", "36/37", "37/38", "36/37/38"
I would be very grateful for any advice.
Georgia
[TABLE="width: 1311"]
<tbody>[TR]
[TD]Col A - Substance
[/TD]
[TD]Col B - CAS number
[/TD]
[TD]Col C - Symbol
[/TD]
[TD]Col D - Risk-phrases
[/TD]
[TD]Col E - Safety phrases
[/TD]
[TD]Col F - Group A items identified
[/TD]
[TD]LInes that should excluded
[/TD]
[/TR]
[TR]
[TD]ACEPHATE
[/TD]
[TD]30560-19-1
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2)-36
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETAL
[/TD]
[TD]105-57-7
[/TD]
[TD]F,Xi
[/TD]
[TD]11-36/38
[/TD]
[TD](2-)9-16-33
[/TD]
[TD]ACETAL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETALDEHYDE
[/TD]
[TD]75-07-0
[/TD]
[TD]F+,Xn
[/TD]
[TD]12-36/37-40
[/TD]
[TD](2-)16-33-36/37
[/TD]
[TD]ACETALDEHYDE
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]ACETAMIDE
[/TD]
[TD]60-35-5
[/TD]
[TD]Xn
[/TD]
[TD]40
[/TD]
[TD](2-)36/37
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc.>90%
[/TD]
[TD]64-19-7
[/TD]
[TD]C
[/TD]
[TD]Oct-35
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc. 25%-90%
[/TD]
[TD][/TD]
[TD]C
[/TD]
[TD]34
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ANHYDRIDE
[/TD]
[TD]108-24-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE
[/TD]
[TD]67-64-1
[/TD]
[TD]F
[/TD]
[TD]11
[/TD]
[TD](2-)9-16-23-33
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE CYANOHYDRIN
[/TD]
[TD]75-86-5
[/TD]
[TD]T+,N
[/TD]
[TD]26/27/28-50
[/TD]
[TD](1/2-)7/9-27-45-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONITRILE
[/TD]
[TD]75-05-8
[/TD]
[TD]F,T
[/TD]
[TD]11-23/24/25
[/TD]
[TD](1/2-)16-27-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETOPHENONE
[/TD]
[TD]98-86-2
[/TD]
[TD]Xn
[/TD]
[TD]22-36
[/TD]
[TD](2-)26
[/TD]
[TD]ACETOPHENONE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLACETONE
[/TD]
[TD]123-54-3
[/TD]
[TD]Xn
[/TD]
[TD]Oct-22
[/TD]
[TD](2-)21-23-24/25
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYL CHLORIDE
[/TD]
[TD]75-36-5
[/TD]
[TD]F,C
[/TD]
[TD]11-14-34
[/TD]
[TD](1/2-)9-16-26-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLENE
[/TD]
[TD]74-86-2
[/TD]
[TD]F+
[/TD]
[TD]5/06/2012
[/TD]
[TD](2-)9-16-33
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-ACETYL-6-METHYL-2H-PYRAN-2,4(3H)-DIONE
[/TD]
[TD]520-45-6
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACLONIFEN
[/TD]
[TD]74070-46-5
[/TD]
[TD]N
[/TD]
[TD]50/53
[/TD]
[TD]60-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE
[/TD]
[TD]302-27-2
[/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE, SALTS
[/TD]
[TD][/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45 Note A
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACROLEIN
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLALDEHYDE
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLAMIDE
[/TD]
[TD]79-06-1
[/TD]
[TD]T
[/TD]
[TD]45-46-24/25-48/23/24/25
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD][/TD]
[TD]Xi
[/TD]
[TD]36/37/38
[/TD]
[TD](2-)26-28
[/TD]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]specified elsewhere in this Annex, conc.³10%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLIC ACID
[/TD]
[TD]79-10-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-36-45 Note D
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLONITRILE
[/TD]
[TD]107-13-1
[/TD]
[TD]F,T
[/TD]
[TD]45-11-23/24/25-38
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADIPIC ACID
[/TD]
[TD]124-04-9
[/TD]
[TD]Xi
[/TD]
[TD]36
[/TD]
[TD="align: right"]-2
[/TD]
[TD]ADIPIC ACID
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADZN
[/TD]
[TD]78-67-1
[/TD]
[TD]E,Xn
[/TD]
[TD]2-11-20/22
[/TD]
[TD](2-)39-41-47
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALACHLOR
[/TD]
[TD]15972-60-8
[/TD]
[TD]Xn
[/TD]
[TD]22-40-43
[/TD]
[TD](2-)36/37/39
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDICARB
[/TD]
[TD]116-06-3
[/TD]
[TD]T+
[/TD]
[TD]27/28
[/TD]
[TD](1/2-)22-36/37-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDRIN
[/TD]
[TD]309-00-2
[/TD]
[TD]T,N
[/TD]
[TD]24/25-40-48/24/25-50/53
[/TD]
[TD](1/2-)22-36/37-45-60-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLETHRIN
[/TD]
[TD]584-79-2
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2-)36
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLIDOCHLOR
[/TD]
[TD]93-71-0
[/TD]
[TD]Xn
[/TD]
[TD]21/22-36/38
[/TD]
[TD](2-)26-28-36/37/39
[/TD]
[TD]ALLIDOCHLOR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLYL ALCOHOL
[/TD]
[TD]107-18-6
[/TD]
[TD]T,N
[/TD]
[TD]10-23/24/25-36/37/38-50
[/TD]
[TD](1/2-)36/37/39-38-45-61
[/TD]
[TD]ALLYL ALCOHOL
[/TD]
[TD]Should exclude
[/TD]
[/TR]
</tbody>[/TABLE]