## SMALL function – INDEX MATCH

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that if you enter 0 (zero) in the row or column argument. The SMALL function then calculates the k-th smallest value of these three values.

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 with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Allow me to explain the formula, the MATCH function returns the location of a specified value in an array och cell range.

MATCH($C$10, $B$3:$B$7, 0)

becomes

MATCH("**D**",{"B";"E";"**D**";"C";"A"},0)

Value D is found in position 3 in this array: {"B";"E";"**D**";"C";"A"}

INDEX($C$3:$E$7, MATCH($C$10, $B$3:$B$7, 0), 0)

becomes

INDEX($C$3:$E$7, 3, 0)

The INDEX function then returns all values on relative row 3 in this cell range $C$3:$E$7: {590, 830, 280}

SMALL(INDEX($C$3:$E$7, MATCH($C$10, $B$3:$B$7, 0), 0), ROWS($A$1:A1))

becomes

SMALL({590, 830, 280}, 1)

and returns 280 in cell C11.

You can also use the SMALL function to match multiple values, make sure you read this post: 5 easy ways to VLOOKUP and return multiple values

### Download Excel *.xlsx file

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.

Extract largest values from two columns

Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]

SMALL function – multiple criteria

The array formula in D10 extracts numbers sorted from small to large from column D if Region is equal to […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

SMALL function with duplicates

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

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