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

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

Related posts:

Search for a cell in a table and then display the column title in excel

Search for a text string in an excel table

Search a table and use the returning value to search another table

Search and display all cells that contain all search strings in excel

Lookup with multiple criteria and display multiple search results using excel formula