SMALL function not showing first result

[Akbarov]

Hello dear users,

i use this formula =IFERROR(SMALL($C$2:$C$991,B2),"") to get numbers from c2:c1000 there are 3 results but formula shows only 2 results and if there 10 results it shows 9. can anybody help me please?

C contains this formula : =IF(ISNUMBER(SEARCH(Sheet1!$K$2,A2)),B2,"")


Regards

 

[sheetspread]

C2:C991 isn't C2:C1000. And what's in B2? Should it have $ absolute references?

 

[Akbarov]

A contain names

B has ROW() formula

C =IF(ISNUMBER(SEARCH(Sheet1!$K$2,A2)),B2,"")

D =IFERROR(SMALL($C$2:$C$991,B2),"")

SHEET1K2 is serach box



[TABLE="width: 216"]
<colgroup><col width="72" span="3" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72, align: right"]2[/TD]
[TD="width: 72"][/TD]
[TD="width: 72, align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]124[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]142[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]146[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]153[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD="align: right"]166[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD="align: right"]168[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD="align: right"]174[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]181[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD="align: right"]192[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD="align: right"]195[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD="align: right"]205[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="align: right"]217[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD="align: right"]222[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD="align: right"]223[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

can anybody help me please?

I can tell you why your SMALL function always misses the first value.

Your SMALL functions uses the values in column B as its second argument. As you can see from your sample data, the first value in column B is 2 (not 1), so that SMALL function will return the second smallest value, not the smallest.

Perhaps this would do what you want?
=IFERROR(SMALL($C$2:$C$991,B2-1),"")

 

[Akbarov]

I can tell you why your SMALL function always misses the first value.

Your SMALL functions uses the values in column B as its second argument. As you can see from your sample data, the first value in column B is 2 (not 1), so that SMALL function will return the second smallest value, not the smallest.

Perhaps this would do what you want?
=IFERROR(SMALL($C$2:$C$991,B2-1),"")

Thank you very much Mr.Peter!

 

[Peter_SSs]

Thank you very much Mr.Peter!

No problem, glad to help.