Author: Oscar Cronquist Article last updated on February 16, 2018

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

How to use the SEARCH function

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

How to use the ROW function

The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]

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

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

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)

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

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), "")

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

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:

A record is returned if both search strings are found on the same row

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:

Search each column for a string each and return multiple records – AND logic

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:

Extract records where all criteria match if not empty

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