Functions similar to SMALL or overcome limitations of SMALL

[arvindtechie]

Folks,

I am trying to use the SMALL function to give me the smallest number in the list or range.

I am using this with match and index to get the location of that cell and thereby lookup another value.

Now, one limitation I have noticed is SMALL will give you the lowest first, second and so on values. But when there is another equal number, it doesn't go to that. Is there any way to overcome this? Or any other function perhaps?

Please let me know.

 

[jai9]

Hi,

Instead of SMALL, you can use RANK alongwith COUNTIF to get the required result.

Check this out.

http://www.bestrank.com/blog/making-best-rank-function-in-excel

Jai