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

Question: How do I find rows that contain a specific string value in an excel 2007 table?

Answer:

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

Excel 2007 array formula in B13:E15

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

Replace FIND function with SEARCH function if you don´t want the formula case sensitive.

Named ranges
Table1 (B3:E6)
What is named ranges?

Download excel example file
search-for-a-string-in-an-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

FIND(find_text;within_text;[start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Search for a cell value in an excel table
  2. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  3. Lookup with multiple criteria and display multiple search results using excel formula
  4. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  5. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  6. Search for multiple text strings in multiple cells and use in data validation in excel
  7. Search and display all cells that contain all search strings in excel
  8. Filter unique distinct values where adjacent cells contain search string in excel
  9. Search for multiple text strings in multiple cells in excel