SOLVED

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

Brass Contributor

# 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?

5 Replies

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

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?

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

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

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

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?

best response confirmed by clh_1496 (Brass Contributor)
Solution

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

=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