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 listin
results in order.
The formula I've worked out for a different worksheet which is workin
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, fo
example "e". The spreadsheet I'm using for real is different than th
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 i
greater than 0 i.e. (row(i1)-1), then index the column of number
(column 1), including the header row, then if the search term exists i
column 2, provide the value column 1 of the same row of the Nth instanc
of the search term.
The Nth instance is specified by row($H1) because as I paste thi
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 differen
search terms, the formula works as well because ISNUMBER(SEARCH($B1 wil
turn to B3, B5, etc, referencing the new search terms at the tops o
each column.
I'm a novice so let me know if my explanation is incomplete.
Thanks for all your help!
'Don Guillett[_2_ said:
;1606039']On Wednesday, October 3, 2012 12:46:29 AM UTC-5, excelroofin
wrote:-
Please see attached worksheet. For each "nth" instance of 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|
+-------------------------------------------------------------------+
Pretty simple really
=IF(ISERR(FIND("e",B2)),"",A2
+-------------------------------------------------------------------
+-------------------------------------------------------------------
