This blog article is one out of five articles on the same subject.

Question:

How do I identify rows that have a specific cell value in a excel 2007 table?

Answer:

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

Excel 2007 array formula in B13:E15

=IFERROR(INDEX(Table1, SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1), "") + CTRL + SHIFT + ENTER

Named ranges

Table1 (B3:E6)
What is named ranges?

Array formula explanation

Step 1 - Find cells in named range matching criterion

=IFERROR(INDEX(Table1, SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1), "")

Table1=$C$9

becomes

B3:E6=$C$9

becomes

{East, AA, EE, HH; North, BB, AA, GG; South, CC, FF, DD; West, DD, CC, AA}= AA

becomes

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE, FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE}

Step 2 - Extract row numbers

=IFERROR(INDEX(Table1, SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1), "")

IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, "")

becomes

IF({FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE, FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE}, {1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4}, "")

becomes

{"",1,"","";"","",2,"";"","","","";"","","",4}

Step 3 - Return the k-th smallest row number

SMALL(array,k) returns the k-th smallest number in this data set.

SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1)

becomes

SMALL({"",1,"","";"","",2,"";"","","","";"","","",4}, {1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4})

becmoes

{1,1,1,1;2,2,2,2;4,4,4,4}

Step 4 - Return matching records

INDEX(Table1, SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)

becomes

INDEX(Table1, {1,1,1,1;2,2,2,2;4,4,4,4}, COLUMN(Table1)-MIN(COLUMN(Table1))+1)

becomes

INDEX(Table1, {1,1,1,1;2,2,2,2;4,4,4,4}, {1,2,3,4;1,2,3,4;1,2,3,4;1,2,3,4})

and returns:

{"East","AA","EE","HH";"North","BB","AA","GG";"West","DD","CC","AA"}

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

Functions in this article:

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

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

ROW(reference) returns the rownumber of a reference

COLUMN(reference) Returns the column number of a reference

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

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