| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting cells using multiple criteria | Excel Worksheet Functions | |||
| LOOKUP multiple results but ignore duplicates. | Excel Worksheet Functions | |||
| Counting by multiple criteria | Excel Worksheet Functions | |||
| Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
| How can I do a lookup and get multiple row results? | Excel Worksheet Functions | |||
Linear Mode
