Multi-criteria lookup with Multiple results
 

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.

Multi-criteria lookup with Multiple results
 

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)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"andy62" wrote in message
...
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.

Multi-criteria lookup with Multiple results
 

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))) )

"RagDyer" wrote:

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)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"andy62" wrote in message
...
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.

Multi-criteria lookup with Multiple results
 

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

"andy62" wrote in message
...
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))) )

"RagDyer" wrote:

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)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"andy62" wrote in message
...
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.