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

### 42 Responses to “Search for a text string and return multiple adjacent values”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.

Im trying to solve this problem. I,ve read your posts and they all look great, but what if you dont know what you are looking for? ie searching from a list!

The formula I have searches for words in a text strings, starting with A1, then adds categories from a large list of categories in a table on ANOTHER WORKSHEET 'Dynamic Categories Lists' , depending on the words found in the A1 string. The formula is in B1. The amount of data is huge 19,000 text strings in Column A.

For examples the text string might say:

A B C

1 dog has black spots Dalmatian

2 dog is tall Large Dog

My formula searches for "black spots" and returns " Dalmatians " to B1

My formula searches for " dog is tall" - my formula searches " tall " and return " large dogs" to B2

Formula in B1 is:

=PROPER(IFERROR(LOOKUP(1E+100,SEARCH('Dynamic Categories Lists'!$A$1:$A$1000,A1),'Dynamic Categories Lists'!$A$1:$A$1000),""))

'Dynamic Categories Lists' (DIFFERENT WORKSHEET)

A B

1 Search Word to Find Categories: List Paste

2 black spots Dalmatian

3 tall Large Dog

4 short Small Dog

5 -1000 MORE -1000 MORE

My problem is I need to find the 2nd, 3rd, 4th occurrences

Example

A B C D

1 dog has black spots Dalmatian

2 dog is tall Large Dog

3

4 dog has black spots and is tall Dalmatian Large Dog

A4 "dog has black spots and is tall" I want the formula to return "Dalmatian" & "large dog" to B3

Any help would be appreciated. I have searched heaps of threads and haven’t been able to find the answer!

Thanks for sharing your thoughts. What really I am after:

I download my monthly bank statement in excel. It has cols with date, description of the charges, and the amount debited form my account (to make it simple). There are, say 100 such tractions lines in a given month. What I want, In a different work sheet, in “one” cell I want I want to write a formula to find how much did I spend in store, say, Staples. If a find a line with the this matching word, get the data from next col and keep summing up until last row. Is that too much to ask from excel? I know Ecel is a very powerful spread sheet and I am sure there must be an answer to this. I just could not find it yet. Any help from anybody would be appreciated.

Miah Baset,

Is that too much to ask from excel?Definitely not, use a pivot table:

http://www.excel-easy.com/data-analysis/pivot-tables.html

How would I combine this formula:

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

With a sum formula, so that the multiple instances are summarized into 1 cell.

Example:

A B C

LOOKUP SUM

TX 25 *TX* 110

Lewisville TX 35 *MI* 60

Dallas TX 50

Detroit MI 10

Grand Rapids MI 20

MI 30

How would I combine this formula:

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

With a sum formula, so that the multiple instances are summarized into 1 cell.

Example:

A------ B-----C-----D---------E

------------------LOOKUP----- SUM

TX---25---------- *TX*------- 110

Lewisville TX---35-*MI*------ 60

Dallas TX--- 50

Detroit MI--- 10

Grand Rapids MI--- 20

MI--- 30

Thanks You! Please solve my query here: I have a value in a cell A1 which contains:

M4.CTC.VA03.Verify Sales Documents.v2 [14294], MSUP.CTC.VA03.Verify Sales Documents.v2 [14957], MSUP.CTC.VA03.Verify Sales Documents.v2 [15019], MSUP.CTC.VA03.Verify Sales Documents.v3 [15156]

I would like to have a value in Cell B as: 14294;14957;15019;15156

Basically, it should look for a text "[" and then return all characters after untill reaches "]".

Please help.

Hi, I'm trying to extract two values from a table.

I have multiple rows of data:

Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 PAloc1 PAloc2 PBloc1 PBloc2

FALSE FALSE PAlocBaseline FALSE PAlocOuterAD FALSE PBlocInnerDeuce PBlocLongLong PAlocABaseline #N/A PBlocInnerDeuce PBlocInnerDeuce

FALSE FALSE PAlocBaseline FALSE FALSE PAlocTramAD PBlocInnerDeuce PBlocLongLong PAlocABaseline PAlocBaseline PBlocInnerDeuce PBlocInnerDeuce

FALSE FALSE PAlocBaseline FALSE PAlocOuterAD FALSE PBlocInnerDeuce PBlocLongLong PAlocABaseline PAlocBaseline PBlocInnerDeuce PBlocInnerDeuce

FALSE FALSE PAlocBaseline FALSE FALSE PAlocTramAD PBlocInnerDeuce PBlocLongLong PAlocABaseline PAlocBaseline PBlocInnerDeuce PBlocInnerDeuce

FALSE FALSE PAlocInnerAD FALSE PAlocLongLong FALSE FALSE PBlocBaseline PAlocAInnerAD PAlocInnerAD PBlocBaseline PBlocBaseline

FALSE PAlocLongLong FALSE PAlocInnerAD FALSE FALSE FALSE PBlocDeep PAlocALongLong PAlocLongLong PBlocDeep PBlocDeep

FALSE PAlocLong PAlocOuterAD FALSE FALSE FALSE PBlocOuterDeuce FALSE PAlocALong PAlocLong PBlocOuterDeuce PBlocOuterDeuce

FALSE FALSE PAlocBaseline PAlocInnerAD FALSE FALSE

I need the first instance of where PAloc shows up to populate in PAloc1 and the second instance to populate in PAloc2. The data varies as to when each instance show up.

I tried:

=INDEX(F6:Q6, SMALL(IF(ISNUMBER(SEARCH($F$2, F6:Q6)), MATCH(ROW(F6:Q6:$A$5), ROW(F6:Q6))), ROW(A6)))

Where F2 is "paloc"

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?

I cannot successfully find the sum total of all the amounts for a specific Code (whats the total for R02 or R04, etc). I would love to hear any suggestions

Code Amount

R02 $200

R02 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R04 $200

R05 $200

R05 $200

R05 $200

R05 $200

R11 $200

R11 $200

R11 $200

R11 $200

R11 $200

R11 $200

R11 $200

R11 $200

R11 $200

R16 $300

R16 $300

R16 $300

R16 $200

R16 $300

R16 $300

R16 $300

R16 $300

R16 $300

H 5

H 5

I 6

G 4

H 5

G 4

G 4

J 7

H 5

G 4

G 4

F 3

I 6

G 4

Hey oscar,

If column A has for text like color code above and if D=1 and Z=23. how to get those value in adjacent cell using formula. I have tried IF Isnumber Search but not getting result.

I am using following formula.

=IF(ISNUMBER(SEARCH("D",c3)),"1",IF(ISNUMBER(SEARCH("E",c3)),"2",IF(ISNUMBER(SEARCH("F",c3)),"3",IF(ISNUMBER(SEARCH("G",c3)),"4",IF(ISNUMBER(SEARCH("H",c3)),"5",IF(ISNUMBER(SEARCH("I",c3)),"6",IF(ISNUMBER(SEARCH("J",c3)),"7",IF(ISNUMBER(SEARCH("K",c3)),"8",IF(ISNUMBER(SEARCH("L",c3)),"9",IF(ISNUMBER(SEARCH("M",c3)),"10",IF(ISNUMBER(SEARCH("N",c3)),"11",IF(ISNUMBER(SEARCH("O",c3)),"12",IF(ISNUMBER(SEARCH("P",c3)),"13",IF(ISNUMBER(SEARCH("Q",c3)),"14",IF(ISNUMBER(SEARCH("R",c3)),"15",IF(ISNUMBER(SEARCH("S",c3)),"16",IF(ISNUMBER(SEARCH("T",c3)),"17",IF(ISNUMBER(SEARCH("U",c3)),"18",IF(ISNUMBER(SEARCH("V",c3)),"19",IF(ISNUMBER(SEARCH("W",c3)),"20",IF(ISNUMBER(SEARCH("X",c3)),"21",IF(ISNUMBER(SEARCH("Y",c3)),"22",IF(ISNUMBER(SEARCH("Z",c3)),"23")))))))))))))))))))))))

Shardul

Try this formula:

=CODE(C3)-67