Multi-criteria lookup with Multiple results

[Guest]

I am using the following array-entered formula to retrieve data from another
sheet's column E when the data in columns A and B equal some parameters in my
current sheet:

=INDEX('Meetings List'!$E$1:$E$1000,MATCH(1,('Meetings
List'$A$1:$A$1000=C$1)*('Meetings List'!$B$1:$B$1000=$A4),0),1)

All fine there, but what I need to do is tweak the formula and copy it down
3 more rows so that the set of them will return up to 4 total matches from
column E when my parameters are found in cols A and B. I know the standard
approach for this (not converted to my sheet/column references:
=IF(COLUMNS($E1:E1)<=COUNTIF($A$2:$A$5,$D2),INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D2,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($E1:E1))),"")),
but not sure how to combine the two concepts.

Hope this makes sense. TIA.

 

[RagDyer]

Can you live with the #NUM! errors if you copy down 4 rows and *don't* have
4 matches?

It makes the formula a lot shorter:

=INDEX('Meetings List'!E$1:E$1000,SMALL(IF(('Meetings
List'!A$1:A$1000=$C$1)*('Meetings
List'!B$1:B$1000=$A$4),ROW($1:$1000)),ROWS($1:1)))

 

[Guest]

Awesome, thank you RD. I'll go with a version that traps for errors. But
you did all the hard work:

=IF(ISERROR(INDEX('Meetings List'!E$1:E$1000,SMALL(IF(('Meetings
List'!A$1:A$1000=$C$1)*('Meetings
List'!B$1:B$1000=$A$4),ROW($1:$1000)),ROWS($1:1)))),"",INDEX('Meetings
List'!E$1:E$1000,SMALL(IF(('Meetings List'!A$1:A$1000=$C$1)*('Meetings
List'!B$1:B$1000=$A$4),ROW($1:$1000)),ROWS($1:1))))

 

[Biff]

You don't need to use Index in the error trap: (saves a few keystrokes)

=IF(ISERROR(SMALL(IF((Meetings List!A$1:A$1000=$C$1)*(Meetings
List!B$1:B$1000=$A$4),ROW($1:$1000)),ROWS($1:1))),"",INDEX('Meetings
List'!E$1:E$1000,SMALL(IF(('Meetings List'!A$1:A$1000=$C$1)*('Meetings
List'!B$1:B$1000=$A$4),ROW($1:$1000)),ROWS($1:1))))

This is even a little shorter and slightly more efficient:

=IF(ROWS($1:1)<=SUMPRODUCT(--('Meetings List'!A$1:A$1000=$C$1),--('Meetings
List'!B$1:B$1000=$A$4)),INDEX('Meetings List'!E$1:E$1000,SMALL(IF(('Meetings
List'!A$1:A$1000=$C$1)*('Meetings
List'!B$1:B$1000=$A$4),ROW($1:$1000)),ROWS($1:1))),"")

Biff