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]

Hi Aladin,

It has returned with the same name twice for the 1st and 2nd smallest values. This name didn't work in April (April is the CT column) and so has no value in the pivot. Does the formula believe a no value is the lowest value?

The 3rd name has a high value, so not sure why the formula has returned this person.

My formula is in cell F213 when referencing the name with the 3rd highest value. I have got this correct?

=IF(ROWS(F$213:F213)>COUNTIFS('Bradford - Data'!CT$6:CT$740,"<="&SMALL('Bradford - Data'!CT$6:CT$740,MIN(3,COUNT('Bradford -
Data'!CT$6:CT$740)))),"",INDEX('Bradford - Data'!$CS$6:$CS$740,SMALL(IF('Bradford - Data'!CT$6:CT$740=SMALL('Bradford - Data'!CT$6:CT$740,ROWS(F$213:F213)),ROW('Bradford - Data'!CS$6:CS$740)-ROW('Bradford - Data'!CS$6)+1),SUM(IF(SMALL('Bradford - Data'!CT$6:CT$740,ROW('Bradford - Data'!CS8:CS8)-ROW('Bradford - Data'!CS$6)+1)=SMALL('Bradford - Data'!CT$6:CT$740,ROWS(F213:F$213)),1)))))

Thank you.

 

[marvin jones]

Apologies - i have now used CSE for the formula and it has returned the same person 3 times - the person who didn't work that month. I think the formula thinks a no value is the lowest value and so has returned this.

 

[Aladin Akyurek]

Apologies - i have now used CSE for the formula and it has returned the same person 3 times - the person who didn't work that month. I think the formula thinks a no value is the lowest value and so has returned this.

What does the following return?

=COUNT('Bradford - Data'!CT$6:CT$740)

 

[marvin jones]

62

 

[Aladin Akyurek]

62

So, we have true numbers then. The formula should work as intended.

Book1
	CS	CT
6	jon	5
7	han	3
8	lon	4
9	dan	3
10	can	2
11	ben	1
12	ron	4
Bradford - Data

Book1
	A
2	ben
3	can
4	han
5	han
6
7
Sheet1

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

=IF(ROWS(A$2:A2)>COUNTIFS('Bradford - Data'!CT$6:CT$12,"<="&SMALL('Bradford - Data'!CT$6:CT$12,MIN(3,COUNT('Bradford - Data'!CT$6:CT$12)))),"",INDEX('Bradford - Data'!$CS$6:$CS$12,SMALL(IF('Bradford - Data'!CT$6:CT$12=SMALL('Bradford - Data'!CT$6:CT$12,ROWS(A$2:A2)),ROW('Bradford - Data'!CS$6:CS$12)-ROW('Bradford - Data'!CS$6)+1),SUM(IF(SMALL('Bradford - Data'!CT$6:CT$12,ROW('Bradford - Data'!CS6:CS6)-ROW('Bradford - Data'!CS$6)+1)=SMALL('Bradford - Data'!CT$6:CT$12,ROWS(A$2:A2)),1)))))

 

[marvin jones]

My table is;

CS6 CT6

Name 1 no value

Name 2 no value

Name 3 57.42%

Name 4 no value

Name 5 53.00%

Name 6 no value

Name 7 00.00%

If i enter my old formula of =IF(ISERROR(INDEX('Bradford - Data'!$CS:$CS,MATCH(SMALL('Bradford - Data'!$CT:$CT,3),'Bradford - Data'!$CT:$CT,0))),"TBC",(INDEX('Bradford - Data'!$CS:$CS,MATCH(SMALL('Bradford - Data'!$CT:$CT,3),'Bradford - Data'!$CT:$CT,0)))) i still get a name 3 times, just a different one and
this name has a value of 0.00%.

Is the formula having trouble distinguishing between no values and 0.00%?

Confusing!

 

[Aladin Akyurek]

Do we need to ignore 0 values?

 

[marvin jones]

Yes please.

It's all a bit odd as i replicated your table all in a single worksheet and it worked just fine.

I don't know whether linking it to another tab is the issue.

 

[Aladin Akyurek]

Yes please.

It's all a bit odd as i replicated your table all in a single worksheet and it worked just fine.

I don't know whether linking it to another tab is the issue.

There was a small error in the formula (one $ sign was missing). I'll post the edited formula in what follows.

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
12	ron	4
13	daphne		0.00%
Bradford - Data

Book1
	A	B
1
2	ben	daphne
3	can	lon
4	han	ben
5	dan
6
7
Sheet1

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

=IF(ROWS(A$2:A2)>COUNTIFS('Bradford - Data'!CT$6:CT$20,"<="&SMALL('Bradford - Data'!CT$6:CT$20,MIN(3,COUNT('Bradford - Data'!CT$6:CT$20)))),"",INDEX('Bradford - Data'!$CS$6:$CS$20,SMALL(IF(ISNUMBER('Bradford - Data'!CT$6:CT$20),IF('Bradford - Data'!CT$6:CT$20=SMALL('Bradford - Data'!CT$6:CT$20,ROWS(A$2:A2)),ROW('Bradford - Data'!CS$6:CS$20)-ROW('Bradford - Data'!CS$6)+1)),SUM(IF(SMALL('Bradford - Data'!CT$6:CT$20,ROW('Bradford - Data'!CS$6:CS6)-ROW('Bradford - Data'!CS$6)+1)=SMALL('Bradford - Data'!CT$6:CT$20,ROWS(A$2:A2)),1)))))

 

[Aladin Akyurek]

Do you want to ignore 0 scores like that of Daphne in my example?