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

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.

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

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.

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

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.

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

welcome, glad to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jambruins" wrote in message
...
Thanks, that works great.