# Formula for SMALLEST VALUE not working

#### hrayani

##### Well-known Member
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

 95822 123 95825 123 95828 123 95830 123 95831 123 40041541 127 40043227 127 40042045 129 12943122 A 130 12943122 B 130

<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

##### MrExcel MVP
Wat is the output for the sample you posted?

#### hrayani

##### Well-known Member

For REF # 130 i am getting #NUM!

##### MrExcel MVP

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

##### Well-known Member
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:

##### MrExcel MVP
Let's set C1 to 130.

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

#### hrayani

##### Well-known Member
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.

##### MrExcel MVP
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

##### Well-known Member
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:

#### Sanjeev1976

##### Board Regular
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

1,082,259
Messages
5,364,099
Members
400,779
Latest member
lumers

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...