Search for a cell value in an excel table
This blog article is one out of five articles on the same subject.
- Search for a cell value in an excel table
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
Question:
How do I identify rows that have a specific cell value in a excel 2007 table?
Answer:
Excel 2007 array formula in B13:E15
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.
- Search for a cell value in an excel table
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
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



















Thank you