If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
searching for text strings within a range of cells
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 |
#2
|
|||
|
|||
searching for text strings within a range of cells
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) )) -----Original Message----- 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 . |
Thread Tools | |
Display Modes | |
|
|