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 […]
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.