Index match - multiple criteria

[dunmore83]

Hi,

I have the following formula;

=IFERROR(INDEX('Clinical-Treatments'!$P$12:$P$312,MATCH($D30,'Clinical-Treatments'!$M$12:$M$312,MATCH($L30,'Clinical-Treatments'!$D$12:$D$312,0))),"")

P12:P312 are text values
M12:M312 are numbers
D12:D312 are dates

The formula is returning a match for the 1st date found so it is not matching both the date & the number.

Any ideas on a solution ??

 

[mrhstn]

If your version of Excel has the AGGREGATE function then you could use this

=INDEX($P$12:$P$312, AGGREGATE(15, 6, (ROW($P$12:$P$312)-ROW($P$12)+1)/(($M$12:$M$312=$D$30)*($D$12:$D$312=$L$30)), 1))

If not then the following, which must be confirmed with ctrl+shift+enter (not just enter)

=INDEX($P$12:$P$312, SMALL(IF(($M$12:$M$312=$D$30)*($D$12:$D$312=$L$30), ROW($P$12:$P$312)), 1)-ROW($P$11))

HTH

 

[dunmore83]

Thanks.

My version of excel does have the AGGREGATE function however I cannot get either of the formulas to work - both are returning a #NUM ! error ??

 

[mrhstn]

In that case the value in cell D30 doesn't exist in column M and/or cell L30 doesn't in column D...

What do you get for these two

=MATCH(D30, M12:M312, 0)
=MATCH(L30, D12:D312, 0)

 

[dunmore83]

l

 

[AhoyNC]

Maybe something like this. Not sure how your sheets are laid out.
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag this formula down and across as needed.


Sheet1

	D	E	K	L	M	N	O
29	Trial#			Date	Text Values
30	100			11/1/2017		Trial1
31	101			11/2/2017	Trial2	Trial3	Trial8
32	101			11/15/2017	Trial6
33	108			11/15/2017	Trial7	Trial9

<colgroup><col style="width:30px; "><col style="width:68px;"><col style="width:64px;"><col style="width:64px;"><col style="width:95px;"><col style="width:76px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula M30 {=IFERROR(INDEX('Clinical-Treatments'!$P$12:$P$312,SMALL(IF($D30='Clinical-Treatments'!$M$12:$M$312,IF($L30='Clinical-Treatments'!$D$12:$D$312,ROW('Clinical-Treatments'!$P$12:$P$312)-ROW('Clinical-Treatments'!$P$12)+1)),COLUMNS(#REF!))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Clinical-Treatments

	D	E	L	M	N	O	P
11	Date			Numbers			Text Value
12	11/1/2017			100			Trial1
13	11/2/2017			101			Trial2
14	11/2/2017			101			Trial3
15	11/5/2017			103			Trial4
16	11/2/2017			104			Trial5
17	11/15/2017			101			Trial6
18	11/15/2017			108			Trial7
19	11/2/2017			101			Trial8
20	11/15/2017			108			Trial9
21	11/5/2017			109			Trial10

<colgroup><col style="width:30px; "><col style="width:89px;"><col style="width:64px;"><col style="width:64px;"><col style="width:82px;"><col style="width:64px;"><col style="width:64px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4