The picture above shows you a dataset in cell range B3:E12, the search parameters are in D14:D16. The search results are in B19:E23.

The formula in cell B20 searches for values that meet a range criteria (cell D14 and D15), you can change the column to search in with cell D16.

The picture above shows the array formula and it uses column three (D16) in cell range B3:E12 to find values that match, the formula returns the record if there is a match.

This formula can be used with whatever size and shape of range. To search the first column, type 1 in cell D16.

Update 20 Sep 2017, a smaller formula in cell A19.

Array formula in cell A20:

INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)<=$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1))

See this video to learn more about the formula:

How to enter this array formula

  1. Select cell A20
  2. Paste above formula to cell or formula bar
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter once
  5. Release all keys

The formula bar now shows the formula with a beginning and ending curly bracket, that is if you did the above steps correctly. Like this:


Don't enter these characters yourself, they appear automatically.

Now copy cell A20 and paste to cell range A20:E22.

Recommended post:

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Explaining array formula in cell A20

You can follow along if you select cell A19, go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Step 1 - Filter a specific column in cell range $A$2:$D$11

INDEX($A$2:$D$11, , $C$15, 1)


INDEX($A$2:$D$11, , 3, 1)

and returns C2:C11

INDEX function explained

Fetch a value in a data set based on coordinates.

Step 2 - Check which values are in range

(INDEX($A$2:$D$11, , $C$15, 1)<=$C$14)*(INDEX($A$2:$D$11, , $C$15, 1)>=$C$13)







and returns


Step 3 - Return corresponding row number

IF((INDEX($A$2:$D$11, , $C$15, 1)<=$C$14)*(INDEX($A$2:$D$11, , $C$15, 1)>=$C$13), MATCH(ROW($A$2:$D$11), ROW($A$2:$D$11)), "")


IF({1;0;0;0;1;0;1;1;1;1}, MATCH(ROW($A$2:$D$11), ROW($A$2:$D$11)), "")


IF({1;0;0;0;1;0;1;1;1;1}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns


IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Step 4 - Find k-th smallest row number

SMALL(IF((INDEX($A$2:$D$11, , $C$15, 1)<=$C$14)*(INDEX($A$2:$D$11, , $C$15, 1)>=$C$13), MATCH(ROW($A$2:$D$11), ROW($A$2:$D$11)), ""), ROWS(A19:$A$19))


SMALL({1;"";"";"";5;"";7;8;9;10}, ROWS(A19:$A$19))


SMALL({1;"";"";"";5;"";7;8;9;10}, 1)

and returns 1.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Step 5 - Return entire row from cell range

INDEX($A$2:$D$11, SMALL(IF((INDEX($A$2:$D$11, , $C$15, 1)<=$C$14)*(INDEX($A$2:$D$11, , $C$15, 1)>=$C$13), MATCH(ROW($A$2:$D$11), ROW($A$2:$D$11)), ""), ROWS(A19:$A$19)), , 1)


INDEX($A$2:$D$11, 1, , 1)

and returns cell range $A$2:$A$11 in cell range A19:D19: {1,"John Doe",2,"North"}

INDEX function explained

Fetch a value in a data set based on coordinates.

Download excel sample file for this tutorial

Extract all records that meet criteria in a column.xls
(Excel 97-2003 Workbook *.xls)

Recommended posts

Read this post and see how to extract duplicate records:

Extract duplicate records

This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in […]

Learn how to filter unique distinct records:

Filter unique distinct row records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]