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
Filter unique distinct values where adjacent cells contain search string in excel
Return multiple matches with wildcard vlookup in excel

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

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}

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}

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.

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.

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

Functions in this article:

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

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

MATCH(lookup_valuelookup_array[match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order

ROW(reference) 
Returns the row number of a reference