Find AVERAGE of another column of unsorted list (using INDEX/MATCH ?)

Hi all,

I have an ever-changing list of data with names and scores. I want to match a name to one in the list and find the average of all scores for that particular name.

For example:

Name Y/N Score
Bill Y 90
Tom N 95
Sarah Y 72
Tom Y 89
David Y 90
Bill N 87
Sarah Y 87
Bill Y 93
David Y 88
Steph Y 97
Greg N 91

I want a function that will search all of Bill's scores and get the average for them. I then want the cell below that one to search all of Tom's scores and give me the average...and so on...

I've included an example spreadsheet as well.

Any help would be greatly appreciated.

Thank you in advance,

Seaottr

example.xlsx

Put this array* formula in E2:

Formula:

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$12),0)),"")

then copy down - it will return a list of unique names from column A. Then you can put this formula in F2:

Formula:

=IF(E2="","",AVERAGEIF(A$2:A$12,E2,C$2:C$12))

and copy that down to get the averages.

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

The attached workbook demonstrates the results for you.

Hope this helps.

Pete

Thanks so much Craig, Tony and Pete!!!!