Value return based on match particular text.

[aligahk06]

Dear All,

Please assist me in finding the solution..
I have a column C2:C4156 that contains different text paragraph,I want to match the text based on following condition.
1. if text , "PUMP" OR "HWS" OR "LFS" OR "PNT"OR "QEQ"OR "SFX" match in a cell then formula returns as HYD in a cell J2 and so on.
2.if text , "ADS" OR "SYS" OR "EXS" OR "EXF"OR "SSU"OR "RTS" OR "WWU" match in a cell then formula returns as HVA in a cell J2 and so on.
3.if text , "EXL" OR "INL" OR "TSB" match in a cell then formula returns as LV in a cell J2 and so on.
4.if text , "CSV" OR "TVV" match in a cell then formula returns as LC in a cell J2 and so on.
5.if text , "PABX" match in a cell then formula returns as TEL in a cell J2 and so on.
6.if text , "CDR" OR "DKJ" OR "KTN" match in a cell then formula returns as K-L-E in a cell J2 and so on.
7.if text , "IRR" match in a cell then formula returns as L-N-D in a cell J2 and so on.

rgds,

alig

 

[igold]

Why not write a nested if statement in cell J2 (and so on) with what you have listed here...

 

[Aladin Akyurek]

You could create a 2-column list of corresponding items...

PUMP,HYD
HWS,HYD
…
ADS,HVA
SYS,HVA
…
IRR,L-N-D

Sort the above 2-column list in ascending order on its first column.

Select the first column range and name it keywords via the name box and the second column range values.

Once done, enter in J2 and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&keywords&" "," "&C2&" "),values)

The assumption this formula relies upon is that C2 does not contatin any punctuation like comma, etc.