Index and match with large function

[CorneGeyser]

Hi,

I am using the Large function to calculate the rank and Index and Match to return the user name of that rank. Some of the ranks have similar values and then returns only the first name of that rank value.

I would like it to return the correct user name for each rank value.

 

[Toadstool]

CorneGeyser,

I'm finding it difficult to interpret the question so if you could post examples of your data and formulae that would help.

If I assume you want to list all names where a rank may be the same value (and my assumptions are often wrong) then let me give an example.

Here I have names and scores in columns A & B. In E1 I enter the Rank I want to search (and note in this example Rank=2 or 3 would return the same result, a score of 12). In E2 I use LARGE to find the score for the requested rank.

My solutions in column G, H and I give my suggested formulae.

• G: As you found a simple INDEX and MATCH will only return the first name from the list, so this fails.
• H: I can use an array formula (entered using Ctrl-Shift-Enter so Excel puts curly brackets around) then INDEX and SMALL will return the 1st match, then the second, etc.
• I: Depending on the amount of data an array formula can be slow to run so you can use AGGREGATE and employ the SMALL function (option 15) to get the same result.

	A	B	C	D	E	F	G	H	I
Name	Score	Rank=	Who 1?	Who 2?	Who 3?
Bert	Number	Bert	Bert	Bert
Sue	Bert	Vera	Vera
John	Bert
Alf	Bert
Vera	Bert

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=LARGE(B2:B6,E1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=INDEX($A$2:$A$6,MATCH($E$2,$B$2:$B$6,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]to G6
[/TH]
[TD="align: left"]Copy&Paste as above
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2
[/TH]
[TD="align: left"]=IF(COUNTIF($B$2:$B$6,$E$2)<ROW()-1,"",INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($B$2:$B$6)/($B$2:$B$6=$E$2),ROW()-1)-1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]to I6
[/TH]
[TD="align: left"]Copy&Paste as above
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=IF(COUNTIF($B$2:$B$6,$E$2)<ROW()-1,"",INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=$E$2,ROW($A$2:$A$6)-1),ROW()-1)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]to H6
[/TH]
[TD="align: left"]Copy&Paste as above
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


NOTES:

1. The IF & COUNTIF is to return null once the formula has exhausted valid data to return. COUNTIF will return 2 as there's two entries for rank 12, so I don't want a result in row 4 of the solution.
2. All the row calculations assume headings in row 1 and data starting from row 2. If that's not the case then more maths would be needed in the formulae.

 

[CorneGeyser]

Sorry for the lack of information.

I have a list of users and a value they spent on a specific category of man hours:

[TABLE="class: outer_border, width: 750"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"] A[/TD]
[TD="align: center"] B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Archer, Nico[/TD]
[TD="align: right"]R 8 589.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Forner, Eleanore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]

​

[TABLE="width: 100"]
<tbody>[TR]
[TD="width: 93"]R 112 884.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Baker, Roberts[/TD]
[TD="align: right"]R 8 589.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Erasmus, Michelle[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 25 153.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bosman, Werner[/TD]
[TD="align: right"]R 0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Archer, Nico[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 8 589.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Burelli, Sergio[/TD]
[TD="align: right"]R 7 416.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Archer, Nico[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 8 589.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cibi, Bongani[/TD]
[TD="align: right"]R 0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Archer, Nico[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 8 589.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cilliers, Armand[/TD]
[TD="align: right"]R 0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Burelli, Sergio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 7 416.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]De Luca, Laura[/TD]
[TD="align: right"]R 8 589.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Du Preez, Paul[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 4 972.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Du Preez, Paul[/TD]
[TD="align: right"]R 4 972.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Du Preez, Paul[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 4 972.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Erasmus, Michelle[/TD]
[TD="align: right"]R 25 153.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Bosman, Werner[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Forner, Eleanore[/TD]
[TD="align: right"]R 112 884.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][TABLE="width: 152"]
<tbody>[TR="class: outer_border"]
[TD="width: 152"]Bosman, Werner[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 93"]
<tbody>[TR="class: outer_border"]
[TD="width: 93, align: right"]R 0.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Forte, Nicole[/TD]
[TD="align: right"]R 0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Gates, Franco[/TD]
[TD="align: right"]R 4 972.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Hobson, Joshua[/TD]
[TD="align: right"]R 0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Holliday, Corne[/TD]
[TD="align: right"]R 0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Column A & B is my data. In column D I added the rank from 1 to 10.

[TABLE="width: 150"]
<tbody>[TR]
[TD]Column[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD]=INDEX($A$1:$A$14,MATCH(F2,$B$1:$B$14,0))[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD]=LARGE($B$1:$B$14,D1)[/TD]
[/TR]
</tbody>[/TABLE]

As you can see in E3:E5 the user "Archer, Nico" is repeated. This is what I am trying to avoid.

P.S. sorry, not sure how to get the formatting right on the forum.

 

[CorneGeyser]

Furthermore, I realized in the formula you suggested for I2 the range $A$2:$A$6, after the COUNTIF(), does not fit in there. Is there something missing or am I misinterpreting the formula?

 

[AhoyNC]

See if this works for you.

	A	B	C	D	E	F
Archer, Nico	R 8 589.00
Baker, Roberts	R 8 589.00			Forner, Eleanore	R 112 884.00
Bosman, Werner	R 0.00			Erasmus, Michelle	R 25 153.50
Burelli, Sergio	R 7 416.00			Archer, Nico	R 8 589.00
Cibi, Bongani	R 0.00			Baker, Roberts	R 8 589.00
Cilliers, Armand	R 0.00			De Luca, Laura	R 8 589.00
De Luca, Laura	R 8 589.00			Burelli, Sergio	R 7 416.00
Du Preez, Paul	R 4 972.00			Du Preez, Paul	R 4 972.00
Erasmus, Michelle	R 25 153.50			Gates, Franco	R 4 972.00
Forner, Eleanore	R 112 884.00			Bosman, Werner	R 0.00
Forte, Nicole	R 0.00			Cibi, Bongani	R 0.00
Gates, Franco	R 4 972.00
Hobson, Joshua	R 0.00
Holliday, Corne	R 0.00

<colgroup><col style="width:30px; "><col style="width:133px;"><col style="width:107px;"><col style="width:64px;"><col style="width:64px;"><col style="width:137px;"><col style="width:145px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

</tbody>

[TABLE="width: 747"]
<colgroup><col width="747"></colgroup><tbody>[TR]
   [TD="width: 747"]INDEX($A$1:$A$14,AGGREGATE(15,6,(ROW($A$1:$A$14)-ROW($A$1)+1)/($B$1:$B$14=$F2),COUNTIF($F$2:F2,F2)))[/TD]
 [/TR]
</tbody>[/TABLE]

Excel tables to the web >> Excel Jeanie HTML 4

 

[CorneGeyser]

Thanks a lot AhoyNC! that works perfect

 

[AhoyNC]

You're welcome. Thanks for the feedback.