Adapting a formula to extract text from ANYWHERE in cell

[surkdidat]

Morning!
I have the formula

=IFERROR(INDEX('[Tracker.xlsm]Master List'!$A$4780:$A$6000,MATCH(A21,MID('[Tracker.xlsm]Master List'!$B$4780:$B$6000,18,9),0)),"")

which extracts the text I want after the 18th character.

However, I have noticed that I am getting a few blank results, which is due to the requested input not being entirely followed. (ie extra spaces, no spaces which then mess up the extraction.

Can the above formua be adapted so that it still looks up A21 in the above formula, but looks for that ANYWHERE in the referenced cells please? (ie after 17 characters, 19 character, 28 characters etc?

 

[RoryA]

You could try:

=IFERROR(INDEX('[Tracker.xlsm]Master List'!$A$4780:$A$6000,MATCH("*"&A21&"*",'[Tracker.xlsm]Master List'!$B$4780:$B$6000,0)),"")

 

[Toadstool]

Hi Surkdidat,

You could remove all space from the searches as long as that doesn't cause an issue.

Surkdidat5.xlsx
	A	B
4780	Rover Dog	XXXXXXXXXXXXXXXXXHello Dog
4781	Kitty Cat	XXXXXXXXXXXXXXXXXHello Cat
4782	Mr Mole	XXXXXXXXXXXXXXXXXHello Mole
4783	Billy Goat	XXXXXXXXXXXXXXXXXHelloGoat
4784	Helen Horse	XXXXXXXXXXXXXXXXXHello Horse
4785
Master List

Surkdidat5.xlsx
	A	B	C	D
20				Result
21	Hello Goat			Billy Goat
Master List

Cell Formulas
Range	Formula
D21	D21	=IFERROR(INDEX($A$4780:$A$6000,AGGREGATE(15,6,ROW($A$4780:$A$6000)-ROW($A$4779)/(ISNUMBER(SEARCH(SUBSTITUTE(A21," ",""),SUBSTITUTE($B$4780:$B$6000," ","")))),1)),"")