## 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.

**Contact Oscar**

You can contact me through this contact form