How to search for a string in a column
Question: How do i create a flexible search formula to search a list?
Answer: The following formula let´s you search for a text string in a data set. Cells containing the text string are returned in cell range C10:C20.
Example, Cell C3 contains the text string "s". Names that contain text string s are instantly shown in cell range C10:C20.
Sheet: Search
Sheet "Names"
Array formula:
How to enter an array formula
- Select cell C10
- Press with left mouse button on in formula bar
- Type above formula
- Press and hold CTRL + SHIFT simultaneously
- Press Enter
- Release all keys
If you did it right, the formula is now surrounded by curly brackets:
Explaining array formula in cell C10
Step 1 - Search for a specific text string in a range
SEARCH(Search!$C$3, Names!$A$2:$A$9))
becomes
SEARCH("s", {"Name"; "Richard Dent"; "Harvey Leeson"; "Mick Rooney"; "Steven Gold"; "Peter McDonald"; "Fred Swanson"; "Jack Door"; "Tim Woods"; 0})
and returns
{#VALUE!; 11; #VALUE!; 1; #VALUE!; 6; #VALUE!; 9}
Step 2 - Check if an error is returned
ISERROR(SEARCH(Search!$C$3, Names!$A$2:$A$9))
becomes
ISERROR({#VALUE!; 11; #VALUE!; 1; #VALUE!; 6; #VALUE!; 9})
and returns
{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}
Step 3 - Create row numbers for those values that are FALSE
IF(ISERROR(SEARCH(Search!$C$3, Names!$A$2:$A$9)), "", MATCH(ROW(Names!$A$2:$A$9), ROW(Names!$A$2:$A$9)))
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, "", {1;2;3;4;5;6;7;8})
and returns
{"";2;"";4;"";6;"";8}
Step 4 - Return the k-th smallest row number
SMALL(IF(ISERROR(SEARCH(Search!$C$3, Names!$A$2:$A$9)), "", MATCH(ROW(Names!$A$2:$A$9), ROW(Names!$A$2:$A$9))), ROW(A1))
becomes
SMALL({"";2;"";4;"";6;"";8}, ROW(A1))
becomes
SMALL({"";2;"";4;"";6;"";8}, 1)
and returns
2
Step 5 - Return name
INDEX(Names!$A$2:$A$9, SMALL(IF(ISERROR(SEARCH(Search!$C$3, Names!$A$2:$A$9)), "", MATCH(ROW(Names!$A$2:$A$9), ROW(Names!$A$2:$A$9))), ROW(A1)))
becomes
INDEX(Names!$A$2:$A$9, 2)
becomes
INDEX({"Richard Dent"; "Harvey Leeson"; "Mick Rooney"; "Steven Gold"; "Peter McDonald"; "Fred Swanson"; "Jack Door"; "Tim Woods"}, 2)
and returns
Harvey Leeson in cell C10.
Get excel *.xlsx file
Search and return multiple values category
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.