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:

Extract all records that meet criteria

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

Array formula in A19:D19

=INDEX(tbl, SMALL(IF((INDEX(tbl, , $C$15, 1)<=$C$14)*(INDEX(tbl, , $C$15, 1)>=$C$13), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(A19:$A$19)), , 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges
tbl (A2:A19)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named range. If your list starts at F3, change A19:$A$19 in the above array formula to $F$2:F2.

$C$13 is a cell reference to a start value criterion.
$C$14 is a cell reference to an end value criterion.
$C$15 is the column number where the above criteria must be met.

Download excel sample file for this tutorial.
Extract all records that meet criteria in a column.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

ROWS(array) returns the number of rows in a reference or an array

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Match two criteria and return multiple rows in excel
  2. Extract numbers and text from a range using array formula in excel
  3. Extract a unique distinct list by matching items that meet a criterion in excel
  4. Extract dates and adjacent value in a range using a date critera in excel
  5. Extract cell values in a range using a criterion in excel
  6. Lookup two index columns using min max values and a date range as criteria
  7. Extract unique values from a range using array formula in excel
  8. Identify largest text value in a column using array formula in excel
  9. Create a list of all numbers or text in a column in excel
  10. Lookup values in a range using two or more criteria and return multiple matches in excel