## How to create a useful search formula

*Article updated on July 30, 2017*

**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
- Click 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.

### Download excel *.xlsx file

### Functions in this post:

**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

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

**SMALL(**array, k**)
**Returns the k-th smallest number in this data set.

**ROW(**reference**)**

Returns the row number of a reference.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article