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:

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

Related posts:

1. Extract all rows that contain a value between this and that
2. How to create a unique distinct list where other columns meet two criteria
3. Filter unique rows and sort by date using array formula in excel
4. Filter duplicate rows and sort by date using array formula in excel
5. Extract numbers and text from a range using array formula in excel
6. Match two criteria and return multiple rows in excel
7. Extract dates and adjacent value in a range using a date critera in excel
8. Extract cell values in a range using a criterion in excel
9. Create unique distinct list from column where an adjacent column meets criteria
10. Identify largest text value in a column using array formula in excel