## SMALL function with duplicates

*Article last updated on December 12, 2017*

The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers.

The following formula in cell E3 extracts the smallest number from B3:B9.

However, we need to use another formula in the cells below to ignore duplicate values. The formula in cell E4 extracts the second smallest number from B3:B9.

When you copy this formula and paste to cells below it will extract third, fourth, and so on, smallest value ignoring duplicate values.

The MINIFS function returns the smallest value depending on the condition, in this case, it looks for values larger than the previous value in the cell above, meaning it will ignore duplicate numbers.

I can't use this formula in cell E3 because there is no formula above it.

The MINIFS function was introduced in Excel 2016, if you have an earlier version of Excel see example below.

Formula in cell E3:

Array formula in cell E4:

The formula above is an array formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Download Excel *.xlsx file

Lookup min max values within a date range

This post demonstrates how to find minimum and maximum value using two conditions. In this case they are date conditions […]

Find the smallest value in a list that is larger than a number

Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel […]

How to use the MINIFS function

The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form