This article shows how to find smallest value in array using SMALL and IF functions in an *array formula*.

# Syntax

{=SMALL(IF(criteria range = criteria, data range reference), nth)}

# Steps

- Start with =SMALL( function
- Continue with IF(
- Type the criteria range-criteria equation $E$3:$E$10=C$13,
- Select the range that contains the data range $H$3:$H$10)
- Type ) to close the IF function
- Select cell that contains nth value or you can manually enter the value $B14
- Type ) to finish and complete the formula.

# How

The SMALL function is used to find smallest value in array. We can modify the array by using the IF function that provides a logical test. In normal circumstances, a logical test between a range and a value (or a cell that contains a value) returns *#VALUE!* error.

$E$3:$E$10=C$13

To make this test works, the formula should be defined as an *array formula* and this can be done by pressing *Ctrl + Shift + Enter *keys instead of usual *Enter*. *Ctrl + Shift + Enter *combination “automatically” adds curly brackets around the formula. Please note that, you shouldn’t type brackets manually. Once it is defined as an *array formula*, logical test returns an array of *TRUE/FALSE* values.

$E$3:$E$10=C$13 returns *{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE}*

The IF function can use the *TRUE/FALSE* values and return the numerical equivalent for the *TRUE* items. There will still be *FALSE* items in the return array. The SMALL function ignores them and only evaluates the numbers which are *TRUE* items.

IF($E$3:$E$10=C$13,$H$3:$H$10) returns *{48972;FALSE;FALSE;44000;FALSE;87633;80850;80784}*

Finally, the SMALL function returns smallest values in array. It returns the *nth* smallest value in the “modified” array.

=SMALL(IF($E$3:$E$10=C$13,$H$3:$H$10),$B14)

Also see relevant articles how to find nth largest value in a data table, and how to find nth smallest value in a data table.