Index Match multiple 'AND' criteria plus "Or" / partial match criterion

[kcmuppet]

Hello I have an Index and Match formula that returns the date of an event if matches all these criteria

=INDEX(tbl_Events[Event Date],MATCH(1,
(tbl_Events[Patient Number]=[@[Patient Number]])*
(tbl_Events[Event]="Diagnosed Cancer")*    
(tbl_Events[Event Date]>=ReportStartDate)*
(tbl_Events[Event Date]<=ReportEndDate),0))

What is the right syntax to change the text criterion to include partial matches?

I tried to modify it to find either "Diagnosed Cancer" or "Diagnosed Not Cancer" like this:

=INDEX(tbl_Events[Event Date],MATCH(1,
(tbl_Events[Patient Number]=[@[Patient Number]])*
(tbl_Events[Event]="Diagnosed*")*    
(tbl_Events[Event Date]>=ReportStartDate)*
(tbl_Events[Event Date]<=ReportEndDate),0))

But it returned no results (#N/A) even though the data exists and for each Patient in the list the it can only be one of the other Diagnosis.

Thanks

 

[jasonb75]

The * character doesn't work as a wildcard in that situation, the formula will be looking for an actual * in the cells.

You would need to use (LEFT(tbl_Events[Event],9)="Diagnosed") for it to work.

 

[kcmuppet]

The * character doesn't work as a wildcard in that situation, the formula will be looking for an actual * in the cells.

You would need to use (LEFT(tbl_Events[Event],9)="Diagnosed") for it to work.

Thank you. I also found that (ISNUMBER(SEARCH("Diagnosed",tbl_Events[Event]))) also worked

 

[jasonb75]

There are a few ways, I use left because it uses less functions / steps, making it more efficient with a large array.

 

[kcmuppet]

Thank you both,

Updating here to show all three solutions for completeness

Book1
	A	B	C
1	Fruit	Number	Condition 2
2	Apples	5	TRUE
3	Pears	29	TRUE
4	Pears	29	TRUE
5	Tomatoes	4	FALSE
6
7	Average of column B	21
8	Desired average of column B ignoring duplicate in Column A	17	using Dynamic Array formulae
9		17	using Frequency
10		17	without Frequency or Dynamic Array formulae
Sheet1

Cell Formulas
Range	Formula
B7	B7	=AVERAGEIF(Table1[Condition 2],TRUE,Table1[Number])
B8	B8	=AVERAGE(UNIQUE(FILTER(Table1[[Fruit]:[Number]],Table1[Condition 2])))
B9	B9	=AVERAGE(IF(FREQUENCY(IF(Table1[Condition 2]=TRUE,MATCH(Table1[Fruit],Table1[Fruit],0)),ROW(Table1[Fruit])-ROW(Table1[[#Headers],[Fruit]]))>0,Table1[Number]))
B10	B10	=AVERAGE(IFERROR(Table1[Number]/((ROW(Table1[Fruit])-ROW(Table1[[#Headers],[Fruit]]))=MATCH(Table1[Fruit],Table1[Fruit],0))/Table1[Condition 2],FALSE))