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

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}

I am trying to use your formula but ran into an issue. I am using Excel 2013 and stuck at this step (Step 3). MATCH(ROW($A$1:$A$5),ROW($A$1:$A$5) returns FALSE for cells not containing target text (Pen), which is fine, but returns #N/A for cells containing target text; therefore, not converting to row numbers. Any help would be much appreciated. Thank you.

I meant IF(ISNUMBER(SEARCH($E$1, $A$1:$A$5)), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))) returns FALSE for cells not containing target text (Pen), but returns #N/A for cells containing target text.

Koji,

did you enter the formula as an array formula?

Thank you, Oscar. That solved it.

Hi Oscar,

I am trying to do a search on string with possible more than 1 match and return values horizontally. I tried to use your helpful tips however I got error. Do it limited on # of rows? I only have 5244 rows to be search.

Here is an example. Any help would be greatly appreciated.

Sheet 1 Column A:

2088

2088_5252

2085_5258

Sheet 2 Column A:

2088

2085

Expected result on Sheet 2:

Column A

2088

2085

Column B

2088

2085_5258

Column C

2088_5252

Columns go on depending on the # of matches

Thank you so much.

Joey,

Download excel *.xlsx file

Find-text-string-and-return-multiple-values-horizontally.xlsx

Hi, I'm trying to do the following search.

I'd

Column A

86(b)

61(c)

Column B

92(b)

and table

86(a) 1

86(b) 2

86(c) 3

92(a) 1

92(b) 2

92(c) 3

61(a) 1

61(b) 2

61(c) 3

expected result

in one cell, search a1:b2 for 61(?) to return the value from the table which is 3 here,

in 2nd cell, search a1:b2 for 86(?) to return the value from the table which is 2 here, and

in 3rd cell, search a1:b2 for 92(?) to return the value from the table which is 2.

Han Hoe Liw,

Download *.xlsx file

Han-Hoe-Liw.xlsx

Read post:

Lookups in a related table (array formula)

Hi Oscar,

Very useful code. Is it possible to remove the #NUM! that you get? I have tried to use the following code without success:

{=IF(ISERROR($E$2); ""; 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))))}

David

This array formula works in Excel 2007 and above:

=IFERROR(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))), "")

Just wanted to express my thanks for these solutions! Helped me out immensely today.

jchew,

Thank you for commenting.

HI OScar

I need 2 adjacent result displayed.

Thnaks

amman

aman,

Try this array formula:

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

The two adjacent values are in column B and C.

Hi ,

Can anyone help in getting a formula for below .

My requirement : In a given columns list(A1:A14) i need to search for a name from that column and print the total of payee in another column ( example for Jack, print total of Jack amount which is from row C and print it(F5) cell as shown in below .). I have tried with above formula INDEX but i cannot able get the expected results .

A B C D E F

1 Payee Date Amount

2 Jack 10 My results should look like

3 steve 39 Name Total

4 John 150

5 Jack 20 Tom 23

6 steve 200 Jack 30

7 Todd 34 Steve 239

8 Tom 23 Todd 132

9 Todd 98

10

11

12

13

14

Kumar

Kumar,

Formula in cell B12:

=SUMIF($A$2:$A$9,A12,$C$2:$C$9)

Thank you Oscar , it worked!. appreciate your help.

Hi Oscar, i was redirected here after posting a question on another webpage and after manipulating the formulas here. I have the answer i wanted. You are awesome! Thanks!

Tip to the others: I came here looking for a 'loose' vlookup. To further make it 'looser', add an element of NON-case sensitive, I used the function SEARCH instead of FIND. Hope this helps you!

Hi Oscar,

Thank you so much for these forumlas. I have been using this one a lot.

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

Is there a way to take it a step further and only shot unique values using the frequency formula?

CODE

AA_100 John S

AA_100

AA_100

AA_200

AA_200

AA_200

Apologies I had not finished that post before it sent!

Here is an example of my data and what I'd hope the formula would return:

http://postimg.org/image/l61e8j593/9321549a/

Thinking on it SumProduct and frenquency wouldn't work as they don't return text and that's what I'm after.

Let me know if you need more information, thanks in advance!

Samantha,

maybe you are looking for this?

Array formula in cell E4:

=INDEX($A$4:$A$15, SMALL(IF(ISNUMBER(SEARCH($C$1,$A$4:$A$15))*NOT(COUNTIF($E$3:E3, $A$4:$A$15)), MATCH(ROW($A$4:$A$15), ROW($A$4:$A$15)), ""), 1))

[UPDATE]

This post has a smaller better formula.

Filter unique distinct values where adjacent cells contain search string

Thank you very much! This is exactly what I'm after.

Oscar,

Thanks for providing a walkthru of the "search" functionality. I have another lookup for multiple values, but not finding it elsewhere yet.

I want to use that formula, along with another criteria that must be true in order to return a value

Name.......Status.....Job...Date

John.......Done.......cc1...05/05/2014

Rick.......Done.......05....05/06/2014

John,Sam...Done.......z35...05/04/2014

John,Rick..Incomplete.d3d...""

Rick,Sam...Canceled...j3j...05/09/2014

bob,john...Done.......0O0...05/11/2014

I would use the search to find each row with "John" (dropdown links to Name), but how would I further constrain the list so that only "Done" is returned??

Result

Job...Date

cc1...05/05/2014

z35...05/04/2014

0O0...05/11/2014

Thanks in advance.

Andy

Hi Oscar, one question master..

i have this in sheet1

a b c d e f c

1ten P1 $3 P2 $4

2

3

in other sheet i have a vlookup searching for "ten", but im have two providers, in the sheet2 im going to put "P1" in one C5, and other cell the vlookup "=vlookup(C5,sheet1!a1:c:3, ( here mi problem ) for "ten" im want the result of the P1 but maybe next day im want the P2 price, how its de correct formula.. ?

greetings.

Dear Oscar, in trying to train myself on all things Excel, I ran into a problem while trying to return the values horizontally instead of vertically. I tried to teak the array based on some other formula but to no avail. Would you be so kind as to point a little grasshopper in the right direction? Thank you kindly.

Oscar

Need your assistant in writing a formula for searching a string within sting in a column. Very similar to above examples but the difference is that I have 3000 rows (Sheet A) for array columns and about 7000 rows (SheetB) for search criteria. Here is an example of data.

Sheet A

Column A Column B Roll over bed with extra frame 17-1

Queen room with suite 89-0

King room with kitchen and photo frame 14-0

Sheet B

Column A Column D (Expected results)

Frame 17-1 14-0

Kitchen 14-0

Room 89-0 14-0

Hi Oscar

I have a very unnusual request. i need to put in a formula that will return the results in a cell (alphabetical) as a total. eg:

Column A

Detail: cant clear LA1

0

FALSE

FALSE

this information is in one column. I need to have a "total" that will pull through the information that is not 0 or FALSE

Note, only one cell in the column will show info that is not 0 or FALSE

Hi,

I was trying to use this but my case is a little bit different.

I have 2 sheets:

- In sheet 1, i have two columns, one with the long text and the other one with the result

- In sheet 2, i also have two columns, and in first column i have the ID's and on second the description.

So, i want to search the ID's in from sheet 2 in the long text from sheet 1 and insert the description associated to ID in the first sheet.

Take the example:

Sheet 1:

A1 - My ID is unique.

B1 - (blank)

Sheet 2:

A1 - ID

B1 - Identification

Since there's ID on text in sheet1!A1, i want that sheet1!B1 = sheet2!B2. Which means this should also be "Identification"

Could you please help?

Let's say I have following two col of data

BB CC AAB 2

DD GG AA BB 3

HH BBII JJ 4

KK LL MMA 5

NNBB AA DD 6

in any other cell I want to write a formula such that->if you find "LL" any text string in col 1, then return the corresponding number in col B, which is 5. What that formula be? Thanks for your help.