Variable range and text string search...

[nhawthorne]

I have a formula that searches for a specific text string within a large amount of data and returns the cell location of that text string. Now, based on that variable location, I need to search for another text string within a range of 5 rows above that cell. My current formula is this:

{=ADDRESS(MIN(IF(NOT(ISERROR(FIND(I2,'15 Aug 07.txt'!$A$1:$D$35000,1))),ROW('15 Aug 07.txt'!$A$1:$D$35000),"")),1)}

For instance, this may return the value B1= A25, where A25 contains the specific text from the first search. Now based on VARYING cell locations, A25 for example, how can I setup another search within a range of 5 rows above the VARYING cell location, to search for another text string and return a part of the text in the cell that contains the text string?

I assume it would have to be some sort of INDIRECT and OFFSET funtion within a SEARCH function, but I can't figure it out.

N

 

[xenou]

I got thus far:

Book1
	I	J	K	L	M
1	LookFor	Address	AddresswithSheet	SumRowsabove	Nested
2	3	$A$6	'15Aug07.txt'!$A$6	5	5
3
4
5
Sheet1

I hope this helps...I am not 100% sure what you are searching for but in this example I created the offset formula to sum the dynamic range. Hopefully you can adapt to search instead of sum in that range - it is the offset formula (as you guessed) that creates the range.

What is the purpose of this formula?

Regards.

 

[Scott Huish]

It looks like a continuation of this post:

http://www.mrexcel.com/board2/viewtopic.php?p=1391825