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.

 

[Joe4]

Welcome to the Board!

If you use a helper column (column B) that returns the row numbers, here is one way:

=FILTER(A1:A15,(B1:B15>=D3)*(B1:B15<=D4)*(LEFT(A1:A15,LEN(D5))=D5))

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You could try something like this without the helper column

=LET(r,A1:A15,INDEX(FILTER(r,ISNUMBER(SEARCH(D5,r))*(ROW(r)>=D3)*(ROW(r)<=D4),""),1))

 

[Joe4]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You could try something like this without the helper column

=LET(r,A1:A15INDEX(FILTER(r,ISNUMBER(SEARCH(D5,r))*(ROW(r)>=D3)*(ROW(r)<=D4),""),1))

Peter, I think there is a typo in your formula. There should be a comma before the INDEX function, i.e.

=LET(r,A1:A15,INDEX(FILTER(r,ISNUMBER(SEARCH(D5,r))*(ROW(r)>=D3)*(ROW(r)<=D4),""),1))

 

[Peter_SSs]

Peter, I think there is a typo in your formula.

Yes, thanks Joe.
I edited the formula after posting and didn't realise that I had accidentally deleted the comma. I have put it back in now.

 

[CKsteveFFF]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You could try something like this without the helper column

=LET(r,A1:A15,INDEX(FILTER(r,ISNUMBER(SEARCH(D5,r))*(ROW(r)>=D3)*(ROW(r)<=D4),""),1))

Thank you. A helper column in this case was perfectly suitable.
I'm currently unable to use XL2BB on this computer, but will likely post from another computer that I can use it on when I need to ask another question.

It has worked very well with my actual data set.
The only exception is if it finds multiple identical matches, it returns an array giving me a #SPILL error.
How can I make it return only the single result?

 

[Joe4]

The only exception is if it finds multiple identical matches, it returns an array giving me a #SPILL error.
How can I make it return only the single result?

You get a SPILL error when it will return multiple results, but you have something in the cell below it, so it is unable to "spill" over into the cells below to return the other results.

If you only want to return one single result, if multiple records match the conditions, how do you know which one it should return?

 

[CKsteveFFF]

You get a SPILL error when it will return multiple results, but you have something in the cell below it, so it is unable to "spill" over into the cells below to return the other results.

If you only want to return one single result, if multiple records match the conditions, how do you know which one it should return?

When it finds multiple matches, it returns an array with all of the matches in it. All of the array's contents are identical. Just taking the first row of the array would be fine.

 

[Joe4]

Did you try Peter's formula?
It seems to only return the first match for me when I tested it.

 

[Scott Huish]

=LET(r,OFFSET(INDEX(A:A,D3),,,D4-D3+1),INDEX(r,MATCH(D5&"*",r,0)))

substring.xlsx
	A	B	C	D
1	CAT4
2	DOG1		Givens
3	BAT1		RowStart	6
4	COW6		RowEnd	10
5	OWL8		SubString	BAT
6	CAT3
7	DOG5		Result	BAT9
8	BAT9
9	COW8
10	OWL4
11	CAT1
12	DOG6
13	BAT1
14	COW4
15	OWL2
Sheet1

Cell Formulas
Range	Formula
D7	D7	=LET(r,OFFSET(INDEX(A:A,D3),,,D4-D3+1),INDEX(r,MATCH(D5&"*",r,0)))