Excel VLOOKUP and Large commands

[cachj011]

I am trying to do a worksheet with a table of 2 columns: column 1 contains the names of my team students and column 2 contains the marks they obtain every event. I need this table to update both columns according to the marks with highest first. I managed to a point, until there are two students with the same amount of marks. At this point, while the number of students remain the same, if for example there are 3 students with the same marks, the table populate with 3 rows showing the same "first" name it finds and repeat that name for 3 times instead of changing the names. I am using the VLOOKUP function with embedded LARGE function. How can I go around it so that if there are more than 1 student with the same amount of marks, the table will show the next student's name not the first one for all the cells showing the same amount of marks? thanks to everyone.

 

[Eric W]

Welcome to the MrExcel forum!

It's hard to tell without seeing the layout of your sheet, but maybe:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Student	Mark		Student	Mark
2	Ana	60		Giselle	89
3	Bob	75		Iris	88
4	Cyndi	61		Bob	75
5	Dan	66		Fred	75
6	Elaine	61		Dan	66
7	Fred	75		Cyndi	61
8	Giselle	89		Elaine	61
9	Hank	61		Hank	61
10	Iris	88		Ana	60
11	Jim	50		Jim	50
Sheet7

Cell Formulas
Range	Formula
D2:D11	D2	=INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$11)/($B$2:$B$11=E2),COUNTIF(E$2:E2,E2)))
E2:E11	E2	=LARGE($B$2:$B$11,ROWS(E$2:E2))

 

[cachj011]

Thanks mate. I will try it out tomorrow as here is already late at night.

Joe