INDEX/MATCH but show duplicates (first one, then the other)

[RonOliver]

Hi everybody!

I have a list of people in column A and their marks in column B. I'm doing a LARGE function to find the five highest marks and then I'm also using an INDEX/MATCH to find the name that is in the adjacent cell (so that I have the name of the person that got the high mark). My problem comes when the INDEX/MATCH (and LARGE) functions find duplicate marks. For example, if both Sarah and Suzanne got a 8.3 and Sarah's name comes up first in the list, I'm going to get Sarah's name twice. Is there a formula I can use to prevent this from happening?

My formula is as follows:

=INDEX($A$1:$A$35,MATCH(LARGE($B$1:$B$35,2),$B$1:$B$35,0))

.

When I drag to fill (and change the LARGE function's "k" number), I get Sarah's name twice, instead of getting Sarah first and Suzanne right below it (after I drag to fill). I guess this is very easy, but I have no idea how to fix this without using an overly complicated formula.

Thanks for your suggestions!

 

[alansidman]

I used a different approach. Loaded data to power query, Sorted Descending. Added a column of sequential numbers starting with 1. Filtered the sequential numbers for those below 6. Here is the code and sample data for you to review.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Mark", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 6),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

MOCK_DATA.xlsx
	A	B	C	D	E
1	first_name	Mark		first_name	Mark
2	Garrek	53		Gunter	99
3	Gunter	99		Teri	99
4	Katleen	84		Monika	97
5	Page	72		Broddy	96
6	Orville	58		Haskell	95
7	Mari	67
8	Jeremie	29
9	Tadeo	67
10	Katy	87
11	Walden	82
12	Rebekkah	31
13	Agretha	52
14	Amber	72
15	Teri	99
16	Monika	97
17	Haskell	95
18	Seana	80
19	Josephina	41
20	Ginelle	36
21	Ted	89
22	Alvina	57
23	Caresse	30
24	Julian	48
25	Vanny	34
26	Lamond	78
27	Andree	90
28	Deanna	90
29	Bendix	51
30	Edlin	45
31	Siouxie	90
32	Donella	55
33	Broddy	96
34	Clarissa	73
35	Ali	72
36	Jania	42
data

 

[RonOliver]

I used a different approach. Loaded data to power query, Sorted Descending. Added a column of sequential numbers starting with 1. Filtered the sequential numbers for those below 6. Here is the code and sample data for you to review.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Mark", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 6),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

MOCK_DATA.xlsx
	A	B	C	D	E
1	first_name	Mark		first_name	Mark
2	Garrek	53		Gunter	99
3	Gunter	99		Teri	99
4	Katleen	84		Monika	97
5	Page	72		Broddy	96
6	Orville	58		Haskell	95
7	Mari	67
8	Jeremie	29
9	Tadeo	67
10	Katy	87
11	Walden	82
12	Rebekkah	31
13	Agretha	52
14	Amber	72
15	Teri	99
16	Monika	97
17	Haskell	95
18	Seana	80
19	Josephina	41
20	Ginelle	36
21	Ted	89
22	Alvina	57
23	Caresse	30
24	Julian	48
25	Vanny	34
26	Lamond	78
27	Andree	90
28	Deanna	90
29	Bendix	51
30	Edlin	45
31	Siouxie	90
32	Donella	55
33	Broddy	96
34	Clarissa	73
35	Ali	72
36	Jania	42
data

I like your approach. I'd like to avoid using anything outside of formulas, though. But you gave me a wonderful idea. I can get a helper column and sort the list by mark, then take the first five names and marks from that list. If only there was a way to do this using no helper columns (maybe with a LET function). I'll give it some thought and see if I can come up with it. Thanks so much for your input!

 

[AhoyNC]

Try:

Book3
	A	B	C	D	E
1	first_name	Mark		first_name	Mark
2	Garrek	53		Gunter	99
3	Gunter	99		Teri	99
4	Katleen	84		Monika	97
5	Page	72		Broddy	96
6	Orville	58		Haskell	95
7	Mari	67
8	Jeremie	29
9	Tadeo	67
10	Katy	87
11	Walden	82
12	Rebekkah	31
13	Agretha	52
14	Amber	72
15	Teri	99
16	Monika	97
17	Haskell	95
18	Seana	80
19	Josephina	41
20	Ginelle	36
21	Ted	89
22	Alvina	57
23	Caresse	30
24	Julian	48
25	Vanny	34
26	Lamond	78
27	Andree	90
28	Deanna	90
29	Bendix	51
30	Edlin	45
31	Siouxie	90
32	Donella	55
33	Broddy	96
34	Clarissa	73
35	Ali	72
36	Jania	42
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX($A$2:$A$36,AGGREGATE(15,6,(ROW($A$2:$A$36)-ROW($A$2)+1)/($B$2:$B$36=E2),COUNTIF($E$2:E2,E2)))

 

[RonOliver]

Try:

Book3
	A	B	C	D	E
1	first_name	Mark		first_name	Mark
2	Garrek	53		Gunter	99
3	Gunter	99		Teri	99
4	Katleen	84		Monika	97
5	Page	72		Broddy	96
6	Orville	58		Haskell	95
7	Mari	67
8	Jeremie	29
9	Tadeo	67
10	Katy	87
11	Walden	82
12	Rebekkah	31
13	Agretha	52
14	Amber	72
15	Teri	99
16	Monika	97
17	Haskell	95
18	Seana	80
19	Josephina	41
20	Ginelle	36
21	Ted	89
22	Alvina	57
23	Caresse	30
24	Julian	48
25	Vanny	34
26	Lamond	78
27	Andree	90
28	Deanna	90
29	Bendix	51
30	Edlin	45
31	Siouxie	90
32	Donella	55
33	Broddy	96
34	Clarissa	73
35	Ali	72
36	Jania	42
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX($A$2:$A$36,AGGREGATE(15,6,(ROW($A$2:$A$36)-ROW($A$2)+1)/($B$2:$B$36=E2),COUNTIF($E$2:E2,E2)))

Thank you so very much. I didn't know the AGGREGATE function, so I definitely learned something today!

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E
1	first_name	Mark		first_name	Mark
2	Garrek	53		Gunter	99
3	Gunter	99		Teri	99
4	Katleen	84		Monika	97
5	Page	72		Broddy	96
6	Orville	58		Haskell	95
7	Mari	67
8	Jeremie	29
9	Tadeo	67
10	Katy	87
11	Walden	82
12	Rebekkah	31
13	Agretha	52
14	Amber	72
15	Teri	99
16	Monika	97
17	Haskell	95
18	Seana	80
19	Josephina	41
20	Ginelle	36
21	Ted	89
22	Alvina	57
23	Caresse	30
24	Julian	48
25	Vanny	34
26	Lamond	78
27	Andree	90
28	Deanna	90
29	Bendix	51
30	Edlin	45
31	Siouxie	90
32	Donella	55
33	Broddy	96
34	Clarissa	73
35	Ali	72
36	Jania	42
Main

Cell Formulas
Range	Formula
D2:E6	D2	=TAKE(SORT(A2:B36,2,-1),5)
Dynamic array formulas.

 

[RonOliver]

Another option

Fluff.xlsm
	A	B	C	D	E
1	first_name	Mark		first_name	Mark
2	Garrek	53		Gunter	99
3	Gunter	99		Teri	99
4	Katleen	84		Monika	97
5	Page	72		Broddy	96
6	Orville	58		Haskell	95
7	Mari	67
8	Jeremie	29
9	Tadeo	67
10	Katy	87
11	Walden	82
12	Rebekkah	31
13	Agretha	52
14	Amber	72
15	Teri	99
16	Monika	97
17	Haskell	95
18	Seana	80
19	Josephina	41
20	Ginelle	36
21	Ted	89
22	Alvina	57
23	Caresse	30
24	Julian	48
25	Vanny	34
26	Lamond	78
27	Andree	90
28	Deanna	90
29	Bendix	51
30	Edlin	45
31	Siouxie	90
32	Donella	55
33	Broddy	96
34	Clarissa	73
35	Ali	72
36	Jania	42
Main

Cell Formulas
Range	Formula
D2:E6	D2	=TAKE(SORT(A2:B36,2,-1),5)
Dynamic array formulas.

I actually think I like this even better. Much simpler and neat. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.