Search for a text string and return multiple adjacent values
Table of Contents
- Search for a text string in a column and return multiple adjacent values
- Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values
- Search for multiple text strings in a column and return multiple adjacent values
- Search for a text string in multiple columns and return adjacent values
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:
How to enter an array formula
- Copy formula
- Select cell E2
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Copy cell E2
- 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
Let me explain, if AA is found twice in a cell, the adjacent value is also returned twice.
Array formula in cell E3:
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:
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:
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_value, lookup_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
Related posts:
Filter unique distinct values where adjacent cells contain search string in excel
Search for a text string in an excel table
Sum adjacent values using multiple lookup text values in a column in excel
Search for multiple text strings in multiple cells in excel, part 2
Filter records within two dates and search for a text string in excel
Recommended categories
Here is a much shorter alternate
=INDEX(B$1:B$5,SMALL(IFERROR(IF(SEARCH(F$1,A$1:A$5),ROW(B$1:B$5)),""),ROW(A1)))
@sam,
I am guessing Oscar did not post your formula version because it uses IFERROR which is only available on XL2007 and above. The formula in the blog article will work with XL2003 (I don't have earlier versions of Excel to know about them) in addition to XL2007 and above, so Oscar's formula is a more universal one.
sam,
thanks for your contribution!
Maybe I should explain why I use MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5)) and not ROW($A$1:$A$5).
MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5)) returns an array of row numbers regardless of the size and position of the cell range, named range or dynamic named range.
Example,
ROW($A$5:$A$11) returns {5, 6, 7, 8, 9, 10, 11}.
MATCH(ROW($A$5:$A$11), ROW($A$5:$A$11)) returns {1, 2, 3, 4, 5, 6, 7}