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.

Comments(3) Filed in category: Excel, Search and return multiple values

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. […]

Comments(1) Filed in category: Excel, Row

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.

Comments(12) Filed in category: Excel

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.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

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 […]

Comments(0) Filed in category: Excel

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.

Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

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 […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

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? […]

Comments(17) Filed in category: Excel, Search and return multiple values

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 […]

Comments(3) Filed in category: Excel

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 […]

Comments(16) Filed in category: Excel

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) […]

Comments(27) Filed in category: Excel, Search and return multiple values

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 […]

Comments(46) Filed in category: Excel, Search and return multiple values

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 […]

Comments(46) Filed in category: Excel, Search and return multiple values

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 […]

Comments(46) Filed in category: Excel, Search and return multiple values

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 […]

Comments(46) Filed in category: Excel, Search and return multiple values