SOLVED

INDEX, MATCH for largest scores combined with specific criteria within the names

Brass Contributor

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?

 

 

 

 

5 Replies

@clh_1496 

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?

@clh_1496 

=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 @Hans Vogelaar 

 

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

   
scoreagename
55520Adam
15921Bert
72022Chris
55523Doug
68824Eve
52725Frank
16926George
90527Hubert
71228Iris
48521Joe
61230Karl
65831Lambert
92632Morton
60133Norbert
13334Oscar
12221Paul
31536Quentin
14921Rock
5938Samantha
56821Tanja

 

Is this possible?

 

 

best response confirmed by clh_1496 (Brass Contributor)
Solution

@clh_1496 

How about this version (Excel in Microsoft 365/Office 2021/Online):

@clh_1496 

=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.

 
 
1 best response

Accepted Solutions
best response confirmed by clh_1496 (Brass Contributor)
Solution

@clh_1496 

How about this version (Excel in Microsoft 365/Office 2021/Online):

View solution in original post