searching for text strings within a range of cells

[My_Posts]

Hi


I wish to search for Text within a range of cells. The searched
text may or may not have an exact match.

e.g.

- I'm looking for all instances of Harris within a range of cells
- The ranges - say a1:b45 has many names and values against
these names
- A typical range with values could be

Ted Harris........... 45
John Harris.......... 54
John Hopkins......... 42
Peter Green.......... 62
Ted turner........... 85
etc....

- I wish to get all the instances where "Harris" appears
- (i.e.) I'm looking for
- the instance numbers I.e. 1,2 in the above e.g.
or
- the values 45, 54

- Vlookup does not help, because Vlookup matches Exactly or
makes no proper matches at all :-((
- Vlookup will only return values of ONE of the above matches

- Is there a lookup that is customizable or more versatile than
Vlookup ?

Any help is appreciated
Thanks in advance


Sam

 

[Frank Kabel]

Hi
if you just want to COUNT the instances you may use:
=COUNTIF(A1:A100,"*Harris*")

If you want to sum the values of columnm B try
=SUMIF(A1:A100,"*Harris*",B1:B100)

Or do you want a list extrect. If yes you may try the
following array formulas on a separate sheet (emntered
with CTRL+SHIFT+ENTER)>
=INDEX('sheet1'!$A$1:$A$100,SMALL(IF(ISNUMBER(FIND
("Harris",$A$1:$A$100)),ROW($A$1:$A$100)),ROW(1:1)))

and
=INDEX('sheet1'!$B$1:$B$100,SMALL(IF(ISNUMBER(FIND
("Harris",$A$1:$A$100)),ROW($A$1:$A$100)),ROW(1:1)))