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

2		17
3		20
4		21
5		39
6		44
7		55
8		56
9		63
10		64
11		67
12		68
13		69
14		78
15	15	122
16		124
17	17	127
18		128
19		142
20	20	146
21	21	153
22		163
23		166
24		168
25		174
26		181
27		192
28		195
29		205
30		217
31		222
32		223

<colgroup><col width="72" span="3" style="width:54pt"> </colgroup><tbody>
</tbody>

 

[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.