Find Smallest and Next Smallest Value

[Aniphilo]

Hi,

The problem I am having is that my two formulas are finding the same result

Formula 1
=INDEX($D5:$D11,MATCH(SMALL($G5:$G11,1),$G5:$G11,0))

Formula 2
=INDEX($D5:$D11,MATCH(SMALL($G5:$G11,2),$G5:$G11,0))

D5:D11 (staff names)
G5:G11 (number of files)

I am trying to find the staff member with the lowest and then next lowest number of files.

If there are multiple staff with zero files would that make both formulas give the same result?

It's like I need the 2nd formula to calculate the smallest value not equal to Formula 1.

Thank you

 

[Dryver14]

I have managed to do this with 2 columns, Formulas in K2 and J3 are Control shift enter then copy down
formula in J1 is not

	D	E	F	G	H	I	J	K
jane
peter					phil
phil					peter
geoff					mark
tony					yvonne
mark					jane
yvonne					tony

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></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="align: right"]6[/TD]

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

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

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

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

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

[TD="align: right"]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="align: right"]0[/TD]

[TD="align: right"]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="align: right"]8[/TD]

[TD="align: right"]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="align: right"]4[/TD]

[TD="align: right"]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="align: right"]5[/TD]

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

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

</tbody>

Spreadsheet Formulas

Cell Formula J2 =SMALL($G$1:$G$7,1) K2 {=INDEX($D$1:$D$7,MATCH(J2,$G$1:$G$7,0))} J3 {=SMALL(IF($G$1:$G$7>J2,$G$1:$G$7),1)} K3 {=INDEX($D$1:$D$7,MATCH(J3,$G$1:$G$7,0))} J4 {=SMALL(IF($G$1:$G$7>J3,$G$1:$G$7),1)} K4 {=INDEX($D$1:$D$7,MATCH(J4,$G$1:$G$7,0))} J5 {=SMALL(IF($G$1:$G$7>J4,$G$1:$G$7),1)} K5 {=INDEX($D$1:$D$7,MATCH(J5,$G$1:$G$7,0))} J6 {=SMALL(IF($G$1:$G$7>J5,$G$1:$G$7),1)} K6 {=INDEX($D$1:$D$7,MATCH(J6,$G$1:$G$7,0))} J7 {=SMALL(IF($G$1:$G$7>J6,$G$1:$G$7),1)} K7 {=INDEX($D$1:$D$7,MATCH(J7,$G$1:$G$7,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>