In this post we are going to extract multiple text values. We are looking for names and the criteria are two or more states (cell B18:B19) and two or more months (cell D18:D19).

But first there is something we can do with the data. See picture below.

Rearrange data

The data could have been better arranged like:

Date | State | Name

Searching data arranged as above is easy. You have these options:

Array formula

Array formula in B26:

=INDEX($D$4:$D$17, SMALL(IF(COUNTIF(search_states, $C$4:$C$17)*COUNTIF(search_dates, $B$4:$B$17), (ROW($B$4:$B$17)-MIN(ROW($B$4:$B$17))+1), ""), ROW(A1))) + CTRL + SHIFT + ENTER.

Copy cell B26 and paste it down as far as needed.

Named ranges

search_states (B22:B23)
search_dates (D22:D24)
What is named ranges?

What if you don´t want to rearrange your data? Think again!

Ok, so you don´t want to rearrange your data

Array formula in B22:

=INDEX(tbl, SMALL(IF(COUNTIF(search_states, tbl)*COUNTIF(search_dates, $B$2:$G$2), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), (SMALL(IF(COUNTIF(search_states, tbl)*COUNTIF(search_dates, $B$2:$G$2), ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_states, tbl)*COUNTIF(search_dates, $B$2:$G$2), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)))*16384+1) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges

tbl (B3:G8)
search_states (B18:B19)
search_dates (D18:D19)
What is named ranges?

If you compare the two array formulas you realize that the last formula is more prone to errors, hard to troubleshoot and somewhat?!? complicated. Rearranging data is not complicated.

Download excel sample file for this tutorial.

(Excel 97-2003 Workbook *.xls)

Functions in this article:

Counts the number of cells within a range that meet the given condition

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.

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

returns the column number of a reference