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

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

{=array_formula}

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)

becomes

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)

becomes

({2;6;4;5;3;9;3;2;0;1}<=\$C\$14)*({2;6;4;5;3;9;3;2;0;1}>=\$C\$13)

becomes

({2;6;4;5;3;9;3;2;0;1}<=3)*({2;6;4;5;3;9;3;2;0;1}>=0)

becomes

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

and returns

{1;0;0;0;1;0;1;1;1;1}

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)), "")

becomes

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

becomes

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

and returns

{1;"";"";"";5;"";7;8;9;10}

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

becomes

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

becomes

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)

becomes

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.

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