Help with a Match formula?

[churmie]

Hi guys

I am after a formula be it vlookup or Match, that can give a result based on a particular word is lying in an adjacent cell. Any help would help save a lot of data input!

Eg....

Key
[TABLE="width: 500"]
<tbody>[TR]
[TD]Angular
[/TD]
[TD]ANGL
[/TD]
[/TR]
[TR]
[TD]Radius
[/TD]
[TD]RADI
[/TD]
[/TR]
[TR]
[TD]Length
[/TD]
[TD]LENG
[/TD]
[/TR]
</tbody>[/TABLE]


Data (Column 1) + Desired Result (Column 2)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 390"]
<tbody>[TR]
[TD]TOLERACE ON ANGULAR DIMENSIONS ±2°
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ANGL
[/TD]
[/TR]
[TR]
[TD]CONTROLLED RADIUS
[/TD]
[TD]RADI
[/TD]
[/TR]
[TR]
[TD]CHAMFER LENGTH
[/TD]
[TD]LENG
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E
1				Key
2	TOLERACE ON ANGULAR DIMENSIONS 2	ANGL		Angular	ANGL
3	CHAMFER LENGTH	LENG		Radius	RADI
4	CONTROLLED RADIUS	RADI		Length	LENG
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX($E$2:$E$4,MATCH(LOOKUP(9.99E+307,SEARCH($D$2:$D$4,A2),$D$2:$D$4),$D$2:$D$4,0))

 

[churmie]

You're a star! Thank you!

If I was to get a cell that doesn't contain any of the key words, is there a way of adding a default answer like 'OTHER'?

Regards

Churmie

 

[AlanY]

this should do it

=IFERROR(INDEX($E$2:$E$4,MATCH(LOOKUP(9.99E+307,SEARCH($D$2:$D$4,A4),$D$2:$D$4),$D$2:$D$4,0)),"others")

 

[churmie]

Brilliant thanks...

 

[AlanY]

you're welcome