Search a range for instances of a text string, return neighboring cell values each ti
 

Please see attached worksheet. For each "nth" instance of a particular text string appearing in a range (a column), I'd like to return the value of the cell to it's left (in the same row).

Search a range for instances of a text string, return neighboringcell values each ti
 

On Wednesday, October 3, 2012 12:46:29 AM UTC-5, excelroofing wrote:
Please see attached worksheet. For each "nth" instance of a particular

text string appearing in a range (a column), I'd like to return the

value of the cell to it's left (in the same row).





+-------------------------------------------------------------------+

|Filename: Spreadsheet Problem.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=609|

+-------------------------------------------------------------------+







--

excelroofing

Pretty simple really
=IF(ISERR(FIND("e",B2)),"",A2)

Thanks but I'm actually trying to do something a little more complex. I'm not just searching B2, but rather a whole range, and then listing results in order.

The formula I've worked out for a different worksheet which is working fine now is like this:

=if(countif($I$2:$I$65,"*"&B$1&"*")(row($I1)-1),INDEX($H$1:$H$65,SMALL(IF(ISNUMBER(SEARCH(B$1,$ I$2:$I$65)),ROW($I$2:$I$65),""),row($H1))),)

The B1 is a cell where I'm putting the term to be searched for, for example "e". The spreadsheet I'm using for real is different than the one I uploaded, which is why you see references to columns I and H.

So I start off saying "if the number of instances of the search term is greater than 0 i.e. (row(i1)-1), then index the column of numbers (column 1), including the header row, then if the search term exists in column 2, provide the value column 1 of the same row of the Nth instance of the search term.

The Nth instance is specified by row($H1) because as I paste this formula down it becomes H2, H3, etc. indicating a value of 1,2,3,etc. for ascending instances.

If I copy the formula to the right in other columns with different search terms, the formula works as well because ISNUMBER(SEARCH($B1 will turn to B3, B5, etc, referencing the new search terms at the tops of each column.

I'm a novice so let me know if my explanation is incomplete.

Thanks for all your help!