This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the same row.

The functions used in most of the formulas on this web page are the SEARCH and FIND functions. They return a number based on the position of a text string in a value, see cell E3 on picture above. If the text string is not found the functions return #VALUE! error.

"fox" is found at character 7 in value "a red fox", see picture below.

The SEARCH function is case-insensitive and the FIND function is case-sensitive. You can replace these functions with each other if you are looking for a case-sensitive formula or vice versa.

The magic starts when you enter the formula as an array formula, this allows you to search an entire cell range for text strings.

Table of Contents

Search for a substring in a column and return multiple corresponding values

John Paul asks:

I need a formula with no Macros – here an example of what I’m trying to do.

Column A contains:
Head-Phones-Sony
Black-Pen,Skilcraft
AAA-Batteries,24pk
Eraser,5pk
Ink-Pen,Fine-Point-Blue

Column B contains:

M412
M123
M784
M143
M572

In Cell D1 I want to ENTER *Pen* and have it list all corresponding values which is Cell A2 & Cell A5

It sounds like a “Lookup one value with multiple corresponding values” but when I use a wildcard in my search it doesn’t work... Do you have a solution for it? Thank you

Answer:

The picture below shows an array formula in cell F4 that searches cell range B3:B7 for the text string in cell F2 "Pen" and returns the adjacent value, on the same row, from column C.

Text string "Pen" is found in cell B4 and B7 so the formula returns adjacent value M123 and M572 to cell range F4:F5.

Array formula in cell F4:

=INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(SEARCH($F$2, $B$3:$B$7)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))), ROWS($A$1:A1)))

Watch a video where I explain the formula above

Back to top

The following article explains how to look for values that contain two different text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Comments(17) Filed in category: Excel, Search and return multiple values

This post explains how to look for strings in a cell value and return multiple corresponding values:

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Comments(8) Filed in category: Excel, Search and return multiple values, Vlookup

Back to top

How to enter an array formula

  1. Copy formula
  2. Select cell E2
  3. Paste formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

Back to top

How to copy array formula

  1. Copy cell E2
  2. Paste to cell range E3:E4

Explaining array formula in cell E2

Step 1 - Search for a specific text string

SEARCH($E$1, $A$1:$A$5)

becomes

SEARCH("Pen", {"Head-Phones-Sony"; "Black-Pen,Skilcraft"; "AAA-Batteries,24pk"; "Eraser,5pk"; "Ink-Pen,Fine-Point-Blue"})

and returns

{#VALUE!;7;#VALUE!;#VALUE!;5}

SEARCH and FIND functions

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

Comments(3) Filed in category: Excel, Search and return multiple values

Step 2 - Check if  values in array contains a a number

ISNUMBER(SEARCH($E$1, $A$1:$A$5))

becomes

ISNUMBER({#VALUE!;7;#VALUE!;#VALUE!;5})

and returns

{FALSE; TRUE; FALSE; FALSE; TRUE}

Step 3 - Convert number to a row number

IF(ISNUMBER(SEARCH($E$1, $A$1:$A$5)), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5)))

becomes

IF({FALSE; TRUE; FALSE; FALSE; TRUE}, MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5)))

becomes

IF({FALSE; TRUE; FALSE; FALSE; TRUE}, {1; 2; 3; 4; 5})

and returns

{FALSE; 2; FALSE; FALSE; 5}

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

Step 5 - Filter n-th smallest row number in array

 SMALL(IF(ISNUMBER(SEARCH($E$1, $A$1:$A$5)), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))), ROW(A1))

becomes

SMALL({FALSE; 2; FALSE; FALSE; 5}, ROW(A1))

becomes

SMALL({FALSE; 2; FALSE; FALSE; 5}, 1)

and returns 2.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

Step 6 - Return adjacent value

 INDEX($B$1:$B$5, SMALL(IF(ISNUMBER(SEARCH($E$1, $A$1:$A$5)), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))), ROW(A1)))

becomes

 INDEX($B$1:$B$5, 2)

becomes

INDEX({"M412";"M123";"M784";"M143";"M572"}, 2)

and returns "M123" in cell E2.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Back to top

Download example *.xlsx file

Search for a text string and return multiple adjacent values.xlsx

Back to top

Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values found

The picture below demonstrates an array formula that searches a cell range for a text string and returns the corresponding value on the same row as many times as the textstring is found in the value.

Example, cell B5 has value "EAAEF DD GG AA BB". Text string "AA" is found twice so the corresponding value on the same row is also returned twice (3).

Array formula in cell F4:

=INDEX($C$3:$C$8, SMALL(IF(COUNTIF($F$3:F3, $C$3:$C$8)<>(LEN($B$3:$B$8)-LEN(SUBSTITUTE($B$3:$B$8, $F$2, "")))/LEN($F$2), MATCH(ROW($B$3:$B$8), ROW($B$3:$B$8)), ""), 1))

Back to top

Download excel *.xlsx file

Return adjacent value if-text-string-is found.xlsx

Back to top

Search for multiple text strings in a column and return multiple adjacent values

This array formula searches for both "Phones" and "Eraser" in column B, if at least one of them is found the corresponding value in column C is returned to F4 and F5.

Array formula in cell F4:

=INDEX($C$1:$C$5, SMALL(IF(ISNUMBER(SEARCH($F$1:$G$1, $A$1:$B$5)), MATCH(ROW($B$1:$B$5), ROW($B$1:$B$5))), ROWS($A$1:A1)))

You can have more than two search strings if you like however they must be arranged horizontally (on the same row). If you want the search strings vertically (on the same column) use the TRANSPOSE function, like this: TRANSPOSE($F$1:$F$2)

How to enter an array formula

Back to top

Download excel *.xlsx file

Search-for-a-text-string-and-return-multiple-adjacent-values.xlsx

Back to top

Search for a text string in multiple columns and return adjacent values

The following picture shows two search text strings in cell range G2:H2, the array formula in cell range G4:G7 searches in both column B and C. If at least one text string is found the corresponding value in column D on the same row is returned to G4:G7.

Array formula in cell G4:

=INDEX($C$1:$C$5, SMALL(IF(ISNUMBER(SEARCH($F$1:$G$1, $A$1:$B$5)), MATCH(ROW($B$1:$B$5), ROW($B$1:$B$5))), ROWS($A$1:A1)))

You can have more than two search strings if you like, however, they must be arranged horizontally (on the same row). If you want the search strings vertically (on the same column) use the TRANSPOSE function, like this: TRANSPOSE($G$1:$G$2)

Back to top

Download excel *.xlsx file

Search-for-a-text-string-and-return-multiple-adjacent-values.xlsx

Back to top

Read more articles about this topic

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Comments(17) Filed in category: Excel, Search and return multiple values

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Comments(3) Filed in category: Excel

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

Lookup with multiple criteria and display multiple search results using excel formula, part 3

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Comments(16) Filed in category: Excel

Search for multiple text strings in multiple columns, one text string in each column. Return values in which all text strings match:

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Comments(27) Filed in category: Excel, Search and return multiple values

Search for a single text string in a single column and return multiple matches.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for multiple text strings in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in multiple columns and return corresponding values.

Lookup with multiple criteria and display multiple search results using excel formula

Question: How do I search a list containing First name column and a last name column? I want to search […]

Comments(47) Filed in category: Excel, Search and return multiple values

Filter unique distinct values where adjacent cells contain search string

Question: How do I create a unique distinct list where adjacent cell values contains a search string? AA102 CA AA103 […]

Comments(16) Filed in category: Excel, Unique distinct values

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Comments(8) Filed in category: Excel, Search and return multiple values, Vlookup

Back to top