# Variable range and text string search...

#### nhawthorne

##### New Member
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:

Code:
``{=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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### xenou

##### MrExcel MVP
I got thus far:
Book1
IJKLM
23\$A\$6'15Aug07.txt'!\$A\$655
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.

Replies
5
Views
535
Replies
8
Views
1K
Replies
2
Views
353
Replies
0
Views
171
Replies
18
Views
2K

1,191,086
Messages
5,984,531
Members
439,895
Latest member
ddkexcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back