Find a substring within a range and return the full string.

[CKsteveFFF]

Given three variables (starting row, ending row, and substring) return the full string of the first cell found in the given range that contains the substring.

What formula can I use in D7 to return the string found within the given rows?
Looking to be able to change the rows as needed. For example, if the RowStart were changed to 1, it would return BAT1 since the range now includes another BAT further up.

I have toyed with various MATCH, SEARCH, VLOOKUP etc, but the variable range is throwing me.

 

[Peter_SSs]

If help is still needed here, could you also please clarify if it is possible that the range being looked at may not start in row 1. For example,

1. Could the data be like below where the range in question is A4:A18?
2. If so, would the expected result be ...
   • BAT1 because you are looking in rows 6 to 10 of the worksheet (yellow rows), or
   • BAT2 because you would are looking in rows 6 to 10 of the range in question (green rows)

23 11 14.xlsm
	A	B	C	D
1
2			Givens
3			RowStart	6
4	CAT4		RowEnd	10
5	DOG1		SubString	BAT
6	BAT1
7	COW6		Result?
8	OWL8
9	BAT2
10	DOG5
11	BAT9
12	COW8
13	OWL4
14	CAT1
15	DOG6
16	BAT1
17	COW4
18	OWL2
Find Substring