Question:How would i change the formula, if the "ITEM" is in column A, the "VALUE" is in column B and the "COMPANY" is in column C? and what is i have another column "NAME" in between columns and ITEM and VALUE

The question can be found in this blog post: Extract all rows that contain a value between this and that

Answer:

The picture below 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 below 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:

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Count values, Excel

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.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

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.

Comments(9) Filed in category: Excel

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.

Comments(12) Filed in category: Excel

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.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

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 […]

Comments(0) Filed in category: Duplicate records, Excel

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 […]

Comments(2) Filed in category: Excel, Unique distinct records