Filter cells in a range using criteria

If you can rearrange your data, read this post:
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
You can use a lot easier array formula if you rearrange your data.

Array formula in B15:

=INDEX(tbl, SMALL(IF(tbl="South Carolina", ROW(tbl)-MIN(ROW(tbl))+1), ROW(1:1)), (SMALL(IF(tbl="South Carolina", ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(1:1))-SMALL(IF(tbl="South Carolina", ROW(tbl)-MIN(ROW(tbl))+1), ROW(1:1)))*16384+1) + CTRL + SHIFT + ENTER

copied down as far as necessary.

Named ranges

tbl (B3:I9)
What is named ranges?

How to implement array formula to your workbook

Change the named ranges. Change "South Carolina" to whatever you want to search for, in the above array formula.

Download excel sample file for this tutorial.

Filter cells in a range using criteria.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

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

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

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

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference