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
=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?
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 text string in an excel table
- 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
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
- Search and display all cells that contain all search strings in excel
- Search for multiple text strings in multiple cells and use in data validation in excel
- Search and display a range of values in excel
- Search for multiple text strings in multiple cells in excel, part 2



Leave a Reply