Index match with one exact criteria and one contains partial text criteria

[yoshik]

Hello,

I'm trying to tweak the usual index match formula with multiple criteria to allow one criterion to be: if the cell contains.
Normal: =INDEX(LOOKUPARRAY,MATCH(VALUE1&VALUE2,LOOKUPCOLUMN1&LOOKUPCOLUMN2,0),1)
What I'm trying to do, but this doesn't work: =INDEX(LOOKUPARRAY,MATCH(VALUE1&("*"&VALUE2*"*"),LOOKUPCOLUMN1&LOOKUPCOLUMN2,0),1)

This might be clearer in an example spreadsheet.

What I want is to return the value from the cell in column A, if column B = Updated Response and column C contains "phq9_"

Multiplecriteriacontainsexample.xlsx
	A	B	C
1	12/8/22 11:16	Manage/Design	Edit report
2	12/8/22 11:14	Manage/Design	abcs, phq9_start
3	12/8/22 11:12	Manage/Design	Copy report
4	12/8/22 7:51	Updated Response	end3wpd2
5	12/8/22 7:51	Sent alert Record CPG1251_LauraS (Preliminary 1 Covid *PT*)	Alert #74
6	12/8/22 7:55	Updated Response	mistakes
7	12/8/22 7:50	Updated Response	abcs, phq9_start
8	12/8/22 7:53	Updated Response	Edit report
Sheet1

Thanks

 

[AhoyNC]

If your version of Excel has the FILTER function try:

Book4
	A	B	C
1	12/8/22 11:16	Manage/Design	Edit report
2	12/8/22 11:14	Manage/Design	abcs, phq9_start
3	12/8/22 11:12	Manage/Design	Copy report
4	12/8/22 7:51	Updated Response	end3wpd2
5	12/8/22 7:51	Sent alert Record CPG1251_LauraS (Preliminary 1 Covid *PT*)	Alert #74
6	12/8/22 7:55	Updated Response	mistakes
7	12/8/22 7:50	Updated Response	abcs, phq9_start
8	12/8/22 7:53	Updated Response	Edit report
9
10
11
12	12/8/22 7:50
13
Sheet1

Cell Formulas
Range	Formula
A12	A12	=FILTER(A1:A8,(B1:B8="Updated Response")*(ISNUMBER(SEARCH("phq9_",C1:C8))))

 

[bebo021999]

Book1
	A	B	C
1	12/08/2022 11:16	Manage/Design	Edit report
2	12/08/2022 11:14	Manage/Design	abcs, phq9_start
3	12/08/2022 11:12	Manage/Design	Copy report
4	12/08/2022 07:51	Updated Response	end3wpd2
5	12/08/2022 07:51	Sent alert Record CPG1251_LauraS (Preliminary 1 Covid *PT*)	Alert #74
6	12/08/2022 07:55	Updated Response	mistakes
7	12/08/2022 07:50	Updated Response	abcs, phq9_start
8	12/08/2022 07:53	Updated Response	Edit report
9
10
11	result
12	12/08/2022 07:50
Sheet2

Cell Formulas
Range	Formula
A12	A12	=LOOKUP(2,1/($B$1:$B$8=B8)/ISNUMBER(SEARCH("phq9_",$C$1:$C$8)),$A$1:$A$8)

 

[shinigamilight]

=INDEX($A$1:$A$8,AGGREGATE(14,6,ROW($A$1:$A$8)/--(--($B$1:$B$8="Updated Response")*IFERROR(SEARCH("phq9_",$C$1:$C$8),0)>0),ROW($A1)))

 

[yoshik]

Thanks for your help!