View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
andy62 andy62 is offline
external usenet poster
 
Posts: 158
Default 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.