Forum Discussion
INDEX, MATCH for largest scores combined with specific criteria within the names
Hi
I have this formula that brings through 5 names based on the top 5 largest scores.
=INDEX('Top CV'!$C$5:$C$24,MATCH(SMALL('Top CV'!$A$5:$A$24,1),'Top CV'!$A$5:$A$24,0))
The issue is that I need to somehow alter this formula so that it bring through the top 5 largest scores for names that only include a specific criteria (for example only those that contain number 9), as at the minute it brings through all names from 1 - 10. Is this possible?
How about this version (Excel in Microsoft 365/Office 2021/Online):
5 Replies
=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.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
