Using Small, Index and Match function when there are numbers the same.

[claz2201]

Hi there,

I have a document where students scores are inputted in column N:AJ with the column headers as the categories.

I am wanting in columns C:E to pick out the 3 lowest scores and their corresponding categories.

I have used the following formula: =(INDEX($N$1:$AJ$1,MATCH(SMALL(N3:AJ3,1),N3:AJ3,0))) which works for 1 value, but there are multiple values the same. I would like the formula to pick the bottom 3 categories, even when they all score the same.

I've tried to put an example below;

Name	1st	2nd	Graph	Number	Algebra	Multiplication
Tony	?	?	5	3	2	2
Jack	?	?	0	0	0	2

<tbody>
</tbody>

Hope you can help!

 

[mart37]

Excel Workbook
	A	B	C	D	E	F	G
1	Name	1st	2nd	Graph	Number	Algebra	Multiplication
2	Tony	Multiplication	Algebra	5	3	2	2
3	Jack	Multiplication	Algebra	0	0	0	2
Sheet

 

[James006]

Hello,

If the three bottom categories do NOT score the same ... you can test following

=INDEX($N$1:$AJ$1,MATCH(SMALL($N$3:$AJ$3,LEFT(C$1,1)),$N$3:$AJ$3,0))

HTH

 

[mart37]

#2 mistake: ROW($1:$4)

 

[Aladin Akyurek]

The formula you mention and the example you post do not agree...

Assuming that Tony is in A2, N1:AJ1 houses categories (items of some sort), and N2:AJ2 the scores of Tony on categories:

In B2 control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($B2:B2)>COUNTIFS($N2:$AJ2,"<="&SMALL($N2:$AJ2,MIN(3,COUNT($N2:$AJ2)))),"",INDEX($N$1:$AJ$1,SMALL(IF($N2:$AJ2=SMALL($N2:$AJ2,COLUMNS($B2:B2)),COLUMN($N2:$AJ2)-COLUMN($N2)+1),SUM(IF(SMALL($N2:$AJ2,COLUMN($N2:N2)-COLUMN($N2)+1)=SMALL($N2:$AJ2,COLUMNS($B2:B2)),1)))))

If you must have this in C2, replace $B2:B2 with $C2:C2.

 

[claz2201]

Thank you to Mart37 who has helped me and suggested the following formula, it works a treat!

=INDEX($N$1:$AJ$1,SUMPRODUCT(LARGE(($N3:$AJ3=SMALL($N3:$AJ3,COLUMN()-2))*TRANSPOSE(ROW($1:$23)),COLUMN()-2)))

 

[Aladin Akyurek]

Are you sure? Have a look at the solution offered in post #5 ...

 

[mart37]

Spreadsheet Formulas

Cell Formula C3 {=INDEX($N$1:$AJ$1,MATCH(SMALL($N3:$AJ3+TRANSPOSE(ROW($1:$23))/1000,COLUMN()-2), $N3:$AJ3+TRANSPOSE(ROW($1:$23))/1000,0))} <tbody> </tbody>

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

<tbody>
</tbody>