Question:

How do I find rows that contain a specific string value in a data set?

Answer:

search-for-a-text-string-in-an-excel-table2

Array formula in B13:

=IFERROR(INDEX($B$3:$E$6, SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1), COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1), "")

Select cell range B13:E16. Click formula bar. Press + CTRL + SHIFT + ENTER.

Replace FIND function with SEARCH function if you don´t want the formula case sensitive.

Download excel example file

search-for-a-string-in-an-excel-table.xlsx
(Excel 2007 Workbook *.xlsx)

Explaining array formula in cell B13

Step 1 - Identify cells containing search string

Find function returns the starting point of one text string within another text string. ISNUMBER(FIND($C$9, $B$3:$E$6)) creates this array in cell B11:E14:

Column B has no cells containing string "AA".

Column C has 1 cell containing string "AA". Cell C3

Column D has 1 cell containing string "AA". Cell D4.

Column E has 1 cell containing string "AA". Cell E5.

Recommended article

SEARCH and FIND functions

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

Step 2 - Return row number

ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, "") returns this array:

If string "AA" is found in a cell in the table the corresponding row number is returned.

Recommended post

ROW function explained

ROW(reference) Returns the row number of a reference. You can enter a reference to a single cell or a cell range. […]

Step 3 - Sort the row numbers from smallest to largest

Small function returns the k-th smallest number.

SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1) returns this array:

Related article

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Step 4 - Return a value at the intersection of a particular row and column

INDEX($B$3:$E$6, SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1), COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1)

INDEX function explained

Fetch a value in a data set based on coordinates.

Step 5 - Remove errors

=IFERROR(INDEX($B$3:$E$6, SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1), COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1), "")

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

Lookup with multiple criteria and display multiple search results using excel formula, part 3

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Search for multiple text strings in multiple columns, one text string in each column. Return values in which all text strings match:

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Search for a single text string in a single column and return multiple matches.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for a text string in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for multiple text strings in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for a text string in multiple columns and return corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]