Lookup of some kind, search in two columns, delimiter in 2nd column

[newbieexcel24]

Hello all,

I have a library of chemicals with their category assigned.

The first column is chemical name, second column is alternative chemical names, third column is category.

Chemical name	Alternative chemical name	Category
Aniline	Phenylamine	TOL
Formic acid	Methanoic acid	ACL
Acetic acid	Ethanoic acid; methane carboxylic acid; Glacial acid	FXL
Sodium hydroxide		ALL
Pyrogallol	1,2,3-Benzenetriol; 1,2,3-Trihydroxybenzene; benzene-1,2,3-triol	LHS

Vlookup	Results
Methanoic acid	ACL
Ethanoic acid	FXL
Anline	TOL
1,2,3-Trihydroxybenzene	LHS

How would I be able to achieve this?

Thanks

 

[KRice]

Two questions:
1. What version of Excel are you using? You can update account details to show this…important because optimal solutions often depend on the version.
2. What are you trying to accomplish? Do you want to transform the source data into a two column table, or do you want to enter a chemical name in the left column and have a formula deliver the category?

 

[SunnyAlv]

Like This ?

=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(F2,$B$2:$B$6)),$C$2:$C$6),VLOOKUP(F2,$A$2:$C$6,3,0))

 

[Peter_SSs]

Like This ?

=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(F2,$B$2:$B$6)),$C$2:$C$6),VLOOKUP(F2,$A$2:$C$6,3,0))

That formula is unreliable. Try swapping the values in A3:C3 with those in A5:C5 and then look at your result for "Ethanoic acid".

 

[newbieexcel24]

Two questions:
1. What version of Excel are you using? You can update account details to show this…important because optimal solutions often depend on the version.
2. What are you trying to accomplish? Do you want to transform the source data into a two column table, or do you want to enter a chemical name in the left column and have a formula deliver the category?

1. I am using Office 365
2. My apologies, it was late when I posted this, I should have put more information in. I'm trying to achieve a full match by looking into the first column, if no match, it then goes onto the second column and look in that for a full match, if still no match, it would then return 'Not found'. The formula will need to take the delimiters ( in the second column in consideration. What I'm looking to do is paste in a list of chemicals which will then return the categories for each of the chemicals - which is the latter you said. I hope that's all the information you need?

 

[Peter_SSs]

1. I am using Office 365

Please update your forum profile with that information (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

24 03 15.xlsm
	A	B	C
1	Chemical name	Alternative chemical name	Category
2	Aniline	Phenylamine	TOL
3	Formic acid	Methanoic acid	ACL
4	Acetic acid	Ethanoic acid; methane carboxylic acid; Glacial acid	FXL
5	Sodium hydroxide		ALL
6	Pyrogallol	1,2,3-Benzenetriol; 1,2,3-Trihydroxybenzene; benzene-1,2,3-triol	LHS
7
8
9	Vlookup	Results
10	Methanoic acid	ACL
11	Ethanoic acid	FXL
12	Aniline	TOL
13	1,2,3-Trihydroxybenzene	LHS
14	Something else	Not found
Lookup

Cell Formulas
Range	Formula
B10:B14	B10	=XLOOKUP(A10,A$2:A$6,C$2:C$6,XLOOKUP("*; "&A10&";*","; "&SUBSTITUTE(B$2:B$6,CHAR(10)," ")&";",C$2:C$6,"Not found",2))

 

[newbieexcel24]

Please update your forum profile with that information (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

24 03 15.xlsm
	A	B	C
1	Chemical name	Alternative chemical name	Category
2	Aniline	Phenylamine	TOL
3	Formic acid	Methanoic acid	ACL
4	Acetic acid	Ethanoic acid; methane carboxylic acid; Glacial acid	FXL
5	Sodium hydroxide		ALL
6	Pyrogallol	1,2,3-Benzenetriol; 1,2,3-Trihydroxybenzene; benzene-1,2,3-triol	LHS
7
8
9	Vlookup	Results
10	Methanoic acid	ACL
11	Ethanoic acid	FXL
12	Aniline	TOL
13	1,2,3-Trihydroxybenzene	LHS
14	Something else	Not found
Lookup

Cell Formulas
Range	Formula
B10:B14	B10	=XLOOKUP(A10,A$2:A$6,C$2:C$6,XLOOKUP("*; "&A10&";*","; "&SUBSTITUTE(B$2:B$6,CHAR(10)," ")&";",C$2:C$6,"Not found",2))

Brilliant, thank you. I was having an headache with the delimiter factor. I appreciate that and I have updated my account.

 

[KRice]

Thanks for the clarification. Initially I thought that you might want to create a flat table (a two-column table consisting of only Chemical/Alternative Names and Category). And from the image, it isn't clear whether the apparent text wrapping after 1,2,3-Benzenetriol; is due to a space or a line feed...which would create a delimiter problem, so some effort is needed to address that (maybe not necessary in reality?). If a flat table were desired and Excel 365 were an option (now I see the former is "no" and the latter "yes"), then the flat can be formed as shown in I2. And if a lookup is desired, the flat table can be created internal to the formula and passed to the lookup formula as in A10. More convoluted that Peter's solution, but the flat table might be useful for other purposes. The formula assumes that no delimiters appear at the end of entries in columns A and B.

Book1 (version 2).xlsb
	A	B	C	D					I	J
1	Chemical name	Alternative chemical name	Category						Chemical name	Category
2	Aniline	Phenylamine	TOL						Aniline	TOL
3	Formic acid	Methanoic acid	ACL						Phenylamine	TOL
4	Acetic acid	Ethanoic acid ; methane carboxylic acid;Glacial acid	FXL						Formic acid	ACL
5	Sodium hydroxide		ALL						Methanoic acid	ACL
6	Pyrogallol	1,2,3-Benzenetriol; 1,2,3-Trihydroxybenzene; benzene-1,2,3-triol	LHS						Acetic acid	FXL
7									Ethanoic acid	FXL
8									methane carboxylic acid	FXL
9									Glacial acid	FXL
10	Methanoic acid	ACL							Sodium hydroxide	ALL
11	Ethanoic acid	FXL							Pyrogallol	LHS
12	Aniline	TOL							1,2,3-Benzenetriol	LHS
13	1,2,3-Trihydroxybenzene	LHS							1,2,3-Trihydroxybenzene	LHS
14	Something else	Not found							benzene-1,2,3-triol	LHS
15	1,2,3-Benzenetriol	LHS
16	glacial acid	FXL
17	benzene	Not found
18	methane carboxylic acid	FXL
Sheet2

Cell Formulas
Range	Formula
I2:J14	I2	=LET(b,SUBSTITUTE($B$2:$B$6,CHAR(10)," "),TRIM(TEXTSPLIT(CONCAT(SUBSTITUTE($A$2:$A$6&IF(b="","",";"&b)&";",";",";"&$C$2:$C$6&"|")),";","|",1)))
B10:B18	B10	=LET(b,SUBSTITUTE($B$2:$B$6,CHAR(10)," "),ft,TRIM(TEXTSPLIT(CONCAT(SUBSTITUTE($A$2:$A$6&IF(b="","",";"&b)&";",";",";"&$C$2:$C$6&"|")),";","|",1)), XLOOKUP(A10,INDEX(ft,,1),INDEX(ft,,2),"Not found"))
Dynamic array formulas.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

.. and for updating your profile.