Need to chck if a keyword is present in a text string and return keyword if yes

Based on your sample file, try this, copied down...
=IF(ISNUMBER(SEARCH(E2,A2,1)),E2,IF(ISNUMBER(SEARCH(F2,A2,1)),F2,""))

edit: this searches for either the keyword (returns keyword) or postal code (returns postal code)

Put this array* formula in B2:

=IF(MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6)))=0,"",INDEX(E:E,MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6))))) & IF(MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))=0,"",INDEX(F:F,MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))))

then copy down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Hope this helps.

Pete

EDIT: Sorry, you won't be able to use the full-column references in the arry formula as you have XL2003 - try this one instead:

=IF(MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6)))=0,"",INDEX(E$1:E$6,MAX(IF(ISNUMBER(SEARCH(E$2:E$6,A2)),ROW(E$2:E$6))))) & IF(MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))=0,"",INDEX(F$1:F$6,MAX(IF(ISNUMBER(SEARCH(F$2:F$6,A2)),ROW(F$2:F$6)))))

Still committed with CSE.

File attached to demonstrate.

Pete

@ Ghozi, nice 1 note though that "find" looks for an exact match (upper and lower), while "search" ignores case. in the examples provided, your formula will work perfectly but if the case of just 1 letter was changed, it would fail

Where would you like the Keyword or Code Returned - Or are you asking for a function??

You could try this one, although it does use a helper column:

Formula:

in G2,copied down:

=ROWS($G$2:G2)



in B2, copied down (keyword):

=IF(ISERROR(INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,A2)))*$G$2:$G$6),1)),"",INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,A2)))*$G$2:$G$6),1))



in c2, copy down (postal code):

=IF(ISERROR(INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,B2)))*$G$2:$G$6),2)),"",INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,B2)))*$G$2:$G$6),2))



in d2, copy down (keyword and postal code):

=IF(ISERROR(INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,C2)))*$G$2:$G$6),1)),"",INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,C2)))*$G$2:$G$6),1)&" "&INDEX($E$2:$F$6,SUMPRODUCT(NOT(ISERROR(SEARCH($E$2:$E$6,C2)))*$G$2:$G$6),2))

See attachment