Finding specific words in a coumn within a text string

[Louis1983]

Hi Please can someone help me.
I have a whole list of serial numbers (H001 to H4569) which I want to find within a multiple text strings.

Example: H2365 ATTEND TO NOT STARTING 384146 REQ, but the serial numbers are either in the beginning, middle or end of the text string.

But the formula also has to return the serial number in the next column

[TABLE="width: 445"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Serial Number[/TD]
[/TR]
[TR]
[TD]H2365 ATTEND TO NOT STARTING 384146 REQ[/TD]
[TD]H2365[/TD]
[/TR]
[TR]
[TD]H1456 ATTEND TO NOT STARTING 384146 REQ[/TD]
[TD]H1456[/TD]
[/TR]
[TR]
[TD]ATTEND TO NOT STARTING H1999 384146 REQ[/TD]
[TD]H1999[/TD]
[/TR]
[TR]
[TD]H2365 ATTEND TO NOT STARTING 384146 REQ H4123[/TD]
[TD]H4123[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

Thanks
Louis

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&SerialList&" "," "&$A2&" "),SerialList)

where SerialList stands for the range which houses the serial numbers of interest.

 

[Louis1983]

This works perfectly, thank you so much.

Thanks
Louis

 

[Aladin Akyurek]

This works perfectly, thank you so much.

Thanks
Louis

You are welcome.