Article updated on February 14, 2018

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

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

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.

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}

How to use the SEARCH function

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

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}

How to use the IF function

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

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.

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

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.

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

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

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

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:

A record is returned if both search strings are found on the same row

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

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

Search each column for a string each and return multiple records – AND logic

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

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

Extract records where all criteria match if not empty

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

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

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

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

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

Lookup with multiple criteria and return multiple search results

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

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

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

Back to top