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

J

Jambruins

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.
 
M

Max

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

Jambruins

Thanks, that works great.

Max said:
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(),"")
Click to expand...
 
Top