Using IF function to insert information

[lily231]

I think its easier to just show you!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Sector[/TD]
[/TR]
[TR]
[TD]LMArchitect[/TD]
[TD]Architect[/TD]
[/TR]
[TR]
[TD]PJKArchitects[/TD]
[TD]Architect[/TD]
[/TR]
[TR]
[TD]PLPlumber[/TD]
[TD]Plumber[/TD]
[/TR]
</tbody>[/TABLE]

I have a list of companies in column one. I want to include the sector in column two. However they are a mix of letters and types. Is it possible to use the IF function or something similar to say for example "IF architect present in A1 THEN write Architect in A2"

It will have to be able to detect architect when it is inside a word, and not standing on its own.

 

[oldbrewer]

[TABLE="width: 715"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD]redarchitectblue[/TD]
[TD]architect[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bluearkitectred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]this formula is used[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=IF(ISERROR(SEARCH("architect",A1)),"","architect")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, if you have a list of all possible sectors then you can try like this, for example:

Excel 2013/2016
	A	B	C	D
1	Company	Sector		List of Sectors
2	LMArchitect	Architect		Architect
3	PJKArchitects	Architect		Plumber
4	PLPlumber	Plumber		Carpenter
5				Baker
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(1,-SEARCH($D$2:$D$5,A2),$D$2:$D$5)

 

[oldbrewer]

FormR - can you explain how your formula works please - especially the minus sign....

 

[FormR]

can you explain how your formula works please - especially the minus sign....

Hi, from the help for LOOKUP()..

If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

Because we can't necessarily know what number the SEARCH() function is going to return to the lookup_vector we turn those results negative. Then by using 1 as the lookup_value we can ensure that we get a match if appropiate.

You can get the same result by using a big lookup value instead, i.e.

=LOOKUP(9.99E+307,SEARCH($D$2:$D$5,A2),$D$2:$D$5)