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

Related posts:

1. Sum values in a range where adjacent cell value equals a criterion in excel
2. Filter duplicate text values in a range using “begins with” criterion in excel
3. Filter unique text values using “begins with” criterion in a range in excel
4. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
5. Lookup values in a range using two or more criteria and return multiple matches in excel
6. Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
7. Extract unique values from a range using array formula in excel
8. Return multiple values if above frequency criterion in excel
9. Extract duplicate text values from a range containing both numerical and text values in excel
10. Extract all rows from a range that meet criteria in one column in excel