Index & Match on 2 conditions with wild char

[rex759]

Hello,
I'm attempting to match the second condition based on a word in column C using a wild character.

I tried many variables using ~, ? And * but cant seem to get it to work.

Any ideas?

Book1
	A	B	C	D	E	F	G
1
2
3
4
5	1111		JackandJillwentupthehill	10
6	2222		MaryandTedwerelatefordinner	11
7	3333		PhilandCarrylosttheircat	12
8	4444		DerekandSammissedthegame	13
9	3333		GraceandJuliaaregoodcooks	11
10
11
12
13
14
15	3333		PhilandCarrylosttheircat			#N/A
16
17
Sheet1

 

[MrKowz]

Try using:

=INDEX($D$5:$D$9,MATCH(1,IF($A$5:$A$9=A15,IF(ISNUMBER(SEARCH("phil",$C$5:$C$9)),1)),0))

Array entered with CTRL+SHIFT+ENTER.

 

[Andrew Poulsom]

Here's a non-array version:

=INDEX($D$5:$D$9,MATCH(1,INDEX(($A$5:$A$9=A15)*(ISNUMBER(SEARCH("Phil",$C$5:$C$9))),),FALSE))

 

[rex759]

Brilliant!

Thank you!

 

[MrKowz]

Brilliant!

Thank you!

Glad we were able to help. Thank you for the feedback.

 

[rex759]

One more question, how do I add a 3rd condtion if column B has "CC"?

match on 2 cond with wild.xlsx
	A	B	C	D	E	F
1
2
3
4
5	1111	aa	JackandJillwentupthehill	10
6	2222	bb	MaryandTedwerelatefordinner	11
7	3333	cc	PhilandCarrylosttheircat	12
8	4444	aa	DerekandSammissedthegame	13
9	3333	bb	GraceandJuliaaregoodcooks	11
10	3333	cc	PhilandCarrylosttheircat	7
11
12
13
14
15	3333	cc	PhilandCarrylosttheircat			12
16
17						12
18
Sheet1

 

[Andrew Poulsom]

Like this:

=INDEX($D$5:$D$9,MATCH(1,INDEX(($A$5:$A$9=A15)*($B$5:$B$9=B15)*(ISNUMBER(SEARCH("Phil",$C$5:$C$9))),),FALSE))

 

[rex759]

Thanks again!