Thanks, that works great.

"Max" wrote:

One way

Assume data in cols F and T running in row2 down

In U2:
=IF(F2="NHL",ROW(),"")
Leave U1 blank

In V2:
=IF(ROWS($1:1)COUNT(U:U),"",INDEX(T:T,SMALL(U:U,R OWS($1:1))))
Select U2:V2, copy down to cover the max expected extent of data in col F.
Hide away col U. Col V returns the required results all neatly bunched at the
top.

If the text "NHL" could be part of a text string in col F, eg: NHL champion
use this instead in U2, copy down:
=IF(ISNUMBER(SEARCH("NHL",F2)),ROW(),"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jambruins" wrote:
I would like a formula that finds all the occurances in the cells in column F
that have the word NHL in them and return the text that is in the
corresponding cell in column T. Thank you.