### Search for a text string in a column and return multiple adjacent values

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

Column A contains:
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

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

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.

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

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

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

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