SMALL Formula not Working with Index and Match

[marvin jones]

Hi All,

I have the formula below which looks at the smallest 3 values in a pivot table (column CT) and returns with the employee name (column CS)

=IF(ISERROR(INDEX('Bradford - Data'!$CS:$CS,MATCH(SMALL('Bradford - Data'!$CT:$CT,1),'Bradford - Data'!$CT:$CT,0))),"TBC",(INDEX('Bradford - Data'!$CS:$CS,MATCH(SMALL('Bradford - Data'!$CT:$CT,1),'Bradford - Data'!$CT:$CT,0))))

However, this formula returns with the same name 3 times. I don't have this issue when applying the LARGE formula.

Is this to do with there being a series of no values in the pivot column (due to the employee not working)?

What is an alternative formula to get the smallest values?

Thanks,

 

[marvin jones]

Ah - your last formula changed the names to 3 different ones. What I'd like is for 0.00% to be included as that is the optimum score to be achieved, just not the blank/no value cells. Thanks Aladin, i do appreciate it.

I can see that your formula has picked out a name with no value and then 2 other names below this first name in the pivot. One of the names has a much higher score (37%)

 

[marvin jones]

Morning Aladin,

Your formula works! I tried it again this morning and it is now returning the 3 smallest values - brilliant, thank you!

My only other thought is that we have limited the columns to CT740, CU740, etc..It is likely that as more data populates the pivot the column will extend to further than CT740. Can the formula accommodate this increase?

Thanks again.

 

[Aladin Akyurek]

Morning Aladin,

Your formula works! I tried it again this morning and it is now returning the 3 smallest values - brilliant, thank you!

My only other thought is that we have limited the columns to CT740, CU740, etc..It is likely that as more data populates the pivot the column will extend to further than CT740. Can the formula accommodate this increase?

Thanks again.

That's great. In what follows we create a dynamic set up...

Book1
	CS	CT	CU
6	jon	5
7	han	3
8	lon	4	57.42%
9	dan	3
10	can	2
11	ben	1	53.00%
12	ron	4
13	daphne		0.00%
14
Bradford

Note that the sheet name is shortened to just Bradford (which is a good idea).

Book1
	A	B	C
1	13
2	4	3
3	ben	daphne
4	can	ben
5	han	lon
6	dan
7
8
9
10
Sheet1

In A1 just enter:

=MATCH(REPT("z",255),Bradford!CS:CS)

In A2 just enter and copy across:

=COUNTIFS(Bradford!CT:CT,"<="&SMALL(Bradford!CT:CT,MIN(3,COUNT(Bradford!CT:CT))))

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

=IF(ROWS(A$3:A3)>A$2,"",INDEX(Bradford!$CS$6:INDEX(Bradford!$CS:$CS,$A$1),SMALL(IF(ISNUMBER(Bradford!CT$6:INDEX(Bradford!CT:CT,$A$1)),IF(Bradford!CT$6:INDEX(Bradford!CT:CT,$A$1)=SMALL(Bradford!CT$6:INDEX(Bradford!CT:CT,$A$1),ROWS(A$3:A3)),ROW(Bradford!CS$6:INDEX(Bradford!CT:CT,$A$1))-ROW(Bradford!CS$6)+1)),SUM(IF(SMALL(Bradford!CT$6:INDEX(Bradford!CT:CT,$A$1),ROW(Bradford!CS$6:CS6)-ROW(Bradford!CS$6)+1)=SMALL(Bradford!CT$6:INDEX(Bradford!CT:CT,$A$1),ROWS(A$3:A3)),1)))))

 

[marvin jones]

I think i got confused by columns and rows in the pivot in my previous question. Would this formula work if CT741, CT742 etc.. was the extension in the pivot, rather than CT, CU, CV being the extension in the pivot?

 

[Aladin Akyurek]

I think i got confused by columns and rows in the pivot in my previous question. Would this formula work if CT741, CT742 etc.. was the extension in the pivot, rather than CT, CU, CV being the extension in the pivot?

Yes. The set up expands and/or shrinks rowwise.

 

[marvin jones]

Great, thank you.

Would i ever need to amend the "z" or 255 part in the REPT formula? What does it do within the formula as a whole?

 

[Aladin Akyurek]

Great, thank you.

Would i ever need to amend the "z" or 255 part in the REPT formula? What does it do within the formula as a whole?

You don’t need to adjust it at all. lt creates a string that lexically would sort as the last item in an ascending order.
When the match function uses this as look up value for looking at a text range, it will pick out the position (row) of the last text value from that range. That position is effectively the last row in which there is data.

 

[marvin jones]

Thank you so much - i really appreciate all of your help.