Formula for SMALLEST VALUE not working

[hrayani]

Hello friends,


I am using this formula to

=SMALL(IF(orders_ref=$C$1,orders_po,""),1)

to get the smallest value in the range. Like 1st to 5th Smallest value.

Here is a sample data

[TABLE="width: 171"]
<tbody>[TR]
[TD]95822[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95825[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95828[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95830[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]95831[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]40041541[/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]40043227[/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]40042045[/TD]
[TD]129[/TD]
[/TR]
[TR]
[TD]12943122 A[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]12943122 B[/TD]
[TD]130
[/TD]
[/TR]
</tbody>[/TABLE]

I am getting the result for Ref # 123 or 127... But the formula is not giving the result for
Ref # 130..... May be coz the Numbers in Column A against Ref # 130 contains some
alphabets with them...

Pls if anyone can let me know how to solve this issue???

Regards,

Humayun

 

[Aladin Akyurek]

Wat is the output for the sample you posted?

 

[hrayani]

Thanks for the reply Aladin

For REF # 130 i am getting #NUM!

 

[Aladin Akyurek]

Thanks for the reply Aladin

For REF # 130 i am getting #NUM!

That's not what I'm asking. A correctly designed formula won't have such a problem. Are you wanting a list of 5 bottom (smallest) values (numbers) and corresponding items?

 

[hrayani]

Are you wanting a list of 5 bottom (smallest) values (numbers) and corresponding items?

Yes Exactly....

Like i will enter a Ref # (130 in this case) in Cell # C1 and i would want the formula to show all the ( Max five Numbers ) from Column A Starting from the lowest in D1 to D5....

Let me know if its still not clear

 

[Aladin Akyurek]

Let's set C1 to 130.

What will be the output, given the sample you posted?

 

[hrayani]

Let's set C1 to 130.

What will be the output, given the sample you posted?

From D1 To D5 we need to enter a formula... All The cells from D1:D5 will look into Cell C1 and return values from Column A
Starting from the lowest.

 

[Aladin Akyurek]

From D1 To D5 we need to enter a formula... All The cells from D1:D5 will look into Cell C1 and return values from Column A
Starting from the lowest.

Try to post the output I ask for...

 

[hrayani]

Will enter 130 in C1

Answer should be (12943122 A) in D1 & (12943122 B) in D2.... Rest D3:D5 should show blank as there are only two values against Ref # 130

 

[Sanjeev1976]

Try =IFERROR(INDEX($A$1:$A$10,SMALL(IF(C$1=$B$1:$B$10,ROW($A$1:$A$10),""),ROW())),"") in D1 entered as an array formula and then copied down till D5