Formula for SMALLEST VALUE not working

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
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

95822123
95825123
95828123
95830123
95831123
40041541127
40043227127
40042045129
12943122 A130
12943122 B130

<tbody>
</tbody>

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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Thanks for the reply Aladin

For REF # 130 i am getting #NUM!
 
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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

 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top