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!
Quote:
Originally Posted by Don Guillett[_2_]
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)
|