Author: Oscar Cronquist Article last updated on October 17, 2011

I have the following lookup values:
pen
eraser
paper
paper clip
and the following item list:
Allan's pen
eraser
Frances' eraser
Jenny's pen
paper
paper clip
pen
red pen

What I’d like to do is search for each lookup value in the item list and return all the items that contain that text string. For example, 'pen' would return (in adjacent cells):

Allan’s pen Jenny’s pen pen red pen
I have spent several days trying to tweak your examples, to no avail. Can you help or point me in the right direction, please? Many thanks.

Array formula in cell B2:

=IFERROR(INDEX(\$A\$8:\$A\$15, SMALL(IF(ISNUMBER(FIND(\$A2, \$A\$8:\$A\$15)), MATCH(ROW(\$A\$8:\$A\$15), ROW(\$A\$8:\$A\$15)), ""), COLUMN(A1))), "")

How to enter array formula

1. Select cell B2
2. Type array formula
3. Press and hold Ctrl + Shift
4. Press Enter once
5. Release all keys

How to copy array formula

1. Select cell B2
2. Copy (Ctrl + c)
3. Select cell range C2:E2
4. Paste (Ctrl + v)
5. Select cell range B2:E2
6. Copy (Ctrl + c)
7. Select cell range B3:E5
8. Paste (Ctrl + v)

Explaining array formula
Read the explanation in the following post:
Lookup with multiple criteria and display multiple search results using excel formula
It is similar.