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,

 

[Aladin Akyurek]

What is your version of Excel?

 

[marvin jones]

2010 - thanks.

 

[Aladin Akyurek]

What is the exact range housing numbers?

What is the corresponding range housing employee names?

 

[marvin jones]

The ranges will be added to as the months go by with extra employees - CT refers to April, CU refers to May and so on - therefore the range cannot be fixed and needs to be flexible.

 

[Aladin Akyurek]

Care to specify those ranges nevertheless?

 

[marvin jones]

Hi Aladin,

Numbers are from CT to DE. When the month of April is added a new column is created which in this case will be DF.
Employee Names is just CS only.

Thanks,

 

[Aladin Akyurek]

Sorry. I want the exact ranges...

 

[marvin jones]

Numbers are

CT6:CT740
CU6:CU740
CV6:CV740
CW6:CW740
CX6:CX740
CY6:CY740
CZ6:CZ740
DA6:DA740
DB6:DB740
DC6:DC740
DD6:DD740
DE6:DE740

Names are

CS6:CS740

Thanks

 

[Aladin Akyurek]

Numbers are

CT6:CT740
CU6:CU740
CV6:CV740
CW6:CW740
CX6:CX740
CY6:CY740
CZ6:CZ740
DA6:DA740
DB6:DB740
DC6:DC740
DD6:DD740
DE6:DE740

Names are

CS6:CS740

Thanks

Thanks. What follows implements the formula in A2 of Sheet1. The associated values are not shown.

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)))))

This delivers the names corresponding to the N (=3) smallest values in CT range of Bradford - Data. It can be copied in order process the data from CU, CV, etc.

Adjust the 12 in the range specification to 740.