Table of Contents

Search for a text string in a column and return multiple adjacent 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:

I don´t think I have posted an answer to this question. Although there are posts that have some similarities:

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


Filter unique distinct values where adjacent cells contain search string in excel

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 in excel

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/Find, Vlookup

Array formula in cell E2:

=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)))

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: Built-in features, Count values, Excel

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, Functions, Search/Find

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, Functions

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, Functions

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: Excel, Functions, Index

Download example *.xlsx file

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

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

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

Let me explain, if AA is found twice in a cell, the adjacent value is also returned twice.

Array formula in cell E3:

=INDEX($B$1:$B$6, SMALL(IF(COUNTIF($E$2:E2, $B$1:$B$6)<>(LEN($A$1:$A$6)-LEN(SUBSTITUTE($A$1:$A$6, $E$1, "")))/LEN($E$1), MATCH(ROW($A$1:$A$6), ROW($A$1:$A$6)), ""), 1))

Download excel *.xlsx file

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

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

Array formula in cell E3:

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

Download excel *.xlsx file

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

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

Array formula in cell F3:

=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))), ROW(A1)))

Download excel *.xlsx file

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