Forum Discussion
INDEX, MATCH for largest scores combined with specific criteria within the names
How about this version (Excel in Microsoft 365/Office 2021/Online):
=IFERROR(INDEX('TOP CV'!$C$5:$C$24,MATCH(1,('TOP CV'!$A$5:$A$24=IFERROR(LARGE(IF(ISNUMBER(SEARCH($B$3,'TOP CV'!$C$5:$C$24)),'TOP CV'!$A$5:$A$24),ROW('TOP CV'!$A1)),""))*ISNUMBER(SEARCH($B$3,'TOP CV'!$C$5:$C$24))*(COUNTIF($B$4:B4,'TOP CV'!$C$5:$C$24)=0),0)),"")
You can try this formula and enter the criteria dynamically in cell B3. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Hi OliverScheurich HansVogelaar
Quadruple Pawn I've edited your workbook to show what I'm looking for. I've added a 3rd column with age, and so the criteria is the age (in this case 21) so I want a formula that looks up the 5 names that have the lowest score but only those that are 21 (aka the criteria).
| score | age | name |
| 555 | 20 | Adam |
| 159 | 21 | Bert |
| 720 | 22 | Chris |
| 555 | 23 | Doug |
| 688 | 24 | Eve |
| 527 | 25 | Frank |
| 169 | 26 | George |
| 905 | 27 | Hubert |
| 712 | 28 | Iris |
| 485 | 21 | Joe |
| 612 | 30 | Karl |
| 658 | 31 | Lambert |
| 926 | 32 | Morton |
| 601 | 33 | Norbert |
| 133 | 34 | Oscar |
| 122 | 21 | Paul |
| 315 | 36 | Quentin |
| 149 | 21 | Rock |
| 59 | 38 | Samantha |
| 568 | 21 | Tanja |
Is this possible?
=IF(ROW()<10,IFERROR(INDEX('TOP CV'!$C$5:$C$24,MATCH(1,('TOP CV'!$A$5:$A$24=IFERROR(SMALL(IF(ISNUMBER(SEARCH($B$3,'TOP CV'!$B$5:$B$24)),'TOP CV'!$A$5:$A$24),ROW('TOP CV'!$A1)),""))*(COUNTIF($B$4:B4,'TOP CV'!$C$5:$C$24)=0),0)),""),"")
You can try this formula and enter the criteria dynamically in cell B3. This time the lowest scores are selected according to your latest request. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. A maximum of 5 names are returned in range B5:B9.
How about this version (Excel in Microsoft 365/Office 2021/Online):