Formula help...find values and grouping together...

[Guest]

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.

 

[Guest]

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,ROWS($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(),"")

 

[Guest]

Thanks, that works great.

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,ROWS($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]

welcome, glad to hear that.