Author: Oscar Cronquist Article last updated on August 24, 2018

 

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:

{=array_formula}

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

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

Recommended post:

How to enter an array formula

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($B$3:$E$12, , $D$16, 1)

becomes

INDEX($B$3:$E$12, , 3, 1)

and returns C2:C11

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

Step 2 - Check which values are in range

(INDEX($B$3:$E$12, , $D$16, 1)<=$D$15)*(INDEX($B$3:$E$12, , $D$16, 1)>=$D$14)

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

becomes

IF({1;0;0;0;1;0;1;1;1;1}, MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), "")

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}

How to use the IF function

Checks 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($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))

becomes

SMALL({1;"";"";"";5;"";7;8;9;10}, ROWS(B20:$B$20))

becomes

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

and returns 1.

How to use the SMALL function

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.

Step 5 - Return entire row from cell range

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

becomes

INDEX($B$3:$E$12, 1, , 1)

and returns cell range $B$3:$E$12 in cell range B20:E20: {1,"John Doe",2,"North"}

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

Download Excel *.xlsx file

Extract-all-rows-that-contain-a-value-between-this-and-that-part-21v3.xlsx

Recommended posts

Read this post and see how to extract duplicate records:

Extract duplicate records

This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]

Learn how to filter unique distinct records:

Filter unique distinct records

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