MATCH using the first value found and skipping all the rest

[RonOliver]

Hi. Posting my sheet:

STUDENTS	SCORES
Student 1	8
Student 2	7		Student 8
Student 3	9
Student 4	5
Student 5	7
Student 6
Student 7	3
Student 8	9
Student 9	6

In D3 I've done data validation with a list of students in column A. I want D4 to award a gold medal (the first image function) to all student who got the top score. a silver one to whomever got the second highest score and a bronze one to whomever got the third highest score. The problem with my formula is it will only give the medal to the first person in the list with the top score, so if I choose a student with the same score, it will show me no medal.

Seems like an easy problem, but I've tried all sorts of things (including a combination of UNIQUE, SORT, CHOOSEROWS, etc) and I can't get it to do what I'd like for it to do. Thanks!

 

[Fluff]

How about

=IFERROR(CHOOSE(RANK(INDEX(B2:B10,MATCH(D3,A2:A10,0)),B2:B10),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/9/99/Zlatna_medalja.png/640px-Zlatna_medalja.png"),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/2/23/Srebrna_medalja.png/640px-Srebrna_medalja.png"),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/3/33/Bronzana_medalja.png/640px-Bronzana_medalja.png")),"")

 

[Fluff]

You're welcome & thanks for the feedback.

 

[RonOliver]

I don't know if the formula was posted correctly, but the formula in D4 is this:

=IF(D3=INDEX(A2:A10,MATCH(LARGE(B2:B10,1),B2:B10,0)),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/9/99/Zlatna_medalja.png/640px-Zlatna_medalja.png"),IF(D3=INDEX(A2:A10,MATCH(LARGE(B2:B10,2),B2:B10,0)),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/2/23/Srebrna_medalja.png/640px-Srebrna_medalja.png"),IF(D3=INDEX(A2:A10,MATCH(LARGE(B2:B10,3),B2:B10,0)),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/3/33/Bronzana_medalja.png/640px-Bronzana_medalja.png"),"")))

 

[RonOliver]

How about

=IFERROR(CHOOSE(RANK(INDEX(B2:B10,MATCH(D3,A2:A10,0)),B2:B10),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/9/99/Zlatna_medalja.png/640px-Zlatna_medalja.png"),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/2/23/Srebrna_medalja.png/640px-Srebrna_medalja.png"),IMAGE("https://upload.wikimedia.org/wikipedia/commons/thumb/3/33/Bronzana_medalja.png/640px-Bronzana_medalja.png")),"")

That works! Thanks very much!