Index Match with 3 criteria inc finding string within longer row

[gb199]

Hi all,

I've searched extensively for the answer to this and come up blank. Is what I'm trying possible?

I am trying to return a description of a particular journal entry. There may be more than one entry which have subtly different descriptions. I need to match the nominal, cost center & also match a keyword within the journal description. However the keyword will be part of a longer string that will include, for example, a date or misc text that is not relevant. I cannot figure out how to do this with Index Match. My formula looks something look this:

=INDEX(O:O,MATCH(1,(M:M=E1)*(K:K="xxxx")*(O:O,"*HELPMEPLEASEMREXCEL*"),0)

I've also tried variants of nested if statements within the small function, but again no joy

Hopefully that makes sense. Can it be done?

 

[gb199]

Apologies the title isn't very clear - I went to write the post itself without finishing the title and then forgot and just submitted it

 

[AhoyNC]

Maybe something like this.
Also, it's usually best not to reference whole columns. If your data won't go below say row 15000, then set your range to O2:O15000.

Book1
	A	B	C	D	E					J	K	L	M	N	O
1	Bill test1 check				wxy
2											rrr		rty		Find a test
3											yui		uio		Bill check
4											xxx		wxy		Bill test1 check
5											hju		qas		Hello
6											xxx		ftr		Another test1
7											jhu		jki		Another test3
Sheet1

Cell Formulas
Range	Formula
A1	A1	=INDEX(O:O,MATCH(1,(M:M=E1)*(K:K="xxx")*(ISNUMBER(SEARCH("test1",O:O))),0))

 

[gb199]

Thank you so much AhoyNC - that worked! What's more I was able to adapt it to return the nth instance of the text being found thanks to your ISNUMBER solution above. There may be a better way to do it, but I worked it as follows:

=INDEX($O:$O,SMALL(IF($K:$K="xxx",IF($M:$M=$E1,IF(ISNUMBER(SEARCH("test1",$O:$O)),ROW($O:$O)-ROW(INDEX($O:$O,1,1))+1))),2))

The two at the end will return the 2nd instance, so change it to whatever instance you need