Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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).
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
|
|||
|
|||
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:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
HOW DO I GET A CELL TO RETURN A NEIGHBORING CELL'S VALUE | Excel Worksheet Functions | |||
return cell address of longest text string in a range | Excel Discussion (Misc queries) | |||
Checking number of uniques instances of values in text string | Excel Programming | |||
find instances & report neighboring values | Excel Discussion (Misc queries) |