# Partial match and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the same row.

The functions used in most of the formulas on this web page are the SEARCH and FIND functions. They return a number based on the position of a text string in a value, see cell E3 on picture above. If the text string is not found the functions return #VALUE! error.

"fox" is found at character 7 in value "a red fox", see picture below.

The SEARCH function is case-insensitive and the FIND function is case-sensitive. You can replace these functions with each other if you are looking for a case-sensitive formula or vice versa.

The magic starts when you enter the formula as an array formula, this allows you to search an entire cell range for text strings.

### Table of Contents

- Partial match and return multiple adjacent values
- Partial match and return multiple adjacent values corresponding to the number of matching values
- Partial match and return multiple adjacent values
- Partial match in multiple columns and return adjacent values
- Search and display all cells that contain all search strings

## 1. Search for a sub string in a column and return multiple corresponding 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:**

The picture below shows an array formula in cell F4 that searches cell range B3:B7 for the text string in cell F2 "Pen" and returns the adjacent value, on the same row, from column C.

Text string "Pen" is found in cell B4 and B7 so the formula returns adjacent value M123 and M572 to cell range F4:F5.

Array formula in cell F4:

### 1.1 Watch a video where I explain the formula above

**Update 1/12/2021** - new dynamic array formula

Dynamic array formula in cell F4:

This formula contains the new FILTER function and works only for Excel 365 subscribers.

The following article explains how to look for values that contain two different text strings:

Recommended articles

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

This post explains how to look for strings in a cell value and return multiple corresponding values:

Recommended articles

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

### 1.2 How to enter an array formula

- Copy formula
- Select cell E2
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter

Recommended article

Recommended articles

Array formulas allows you to do advanced calculations not possible with regular formulas.

### 1.3 How to copy array formula

- Copy cell E2
- Paste to cell range E3:E4

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

Recommended articles

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

**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}

Recommended articles

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

**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.

Recommended articles

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**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.

Recommended articles

Gets a value in a specific cell range based on a row and column number.

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

The picture above demonstrates an array formula that searches a cell range for a text string and returns the corresponding value on the same row as many times as the textstring is found in the value.

Example, cell B5 has value "EAAEF DD GG AA BB". Text string "AA" is found twice so the corresponding value on the same row is also returned twice (3).

Array formula in cell F4:

### Get the Excel file

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

## 3. Search for multiple text strings in a column and return multiple adjacent values

This array formula searches for both "Phones" and "Eraser" in column B, if at least one of them is found the corresponding value in column C is returned to F4 and F5.

Array formula in cell F4:

You can have more than two search strings if you like however they must be arranged horizontally (on the same row). If you want the search strings vertically (on the same column) use the TRANSPOSE function, like this: TRANSPOSE($F$1:$F$2)

**Update 1/12/2021** - new dynamic array formula

Dynamic array formula in cell F4:

This formula contains the new FILTER function and works only for Excel 365 subscribers.

### Get the Excel file

Search for multiple text strings in a column and return multiple adjacent values.xlsx

## 4. Search for a text string in multiple columns and return adjacent values

The picture above shows two search text strings in cell range G2:H2, the array formula in cell range G4:G7 searches in **both** columns B and C. If at least one text string is found the corresponding value in column D on the same row is returned to G4:G7.

Array formula in cell G4:

You can have more than two search strings if you like, however, they must be arranged horizontally (on the same row). If you want the search strings vertically (on the same column) use the TRANSPOSE function, like this: TRANSPOSE($G$1:$G$2)

## 5. Search and display all cells that contain all search strings

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel :

*If the list of strings in column D was to increase to a large number e.g. 15, how would you tell excel to select the range of strings, so that you don't have to select each string "SEARCH($D$3" in the search parameter, as it seems is the case at the moment?*

Array Formula in G3:

Change the following in order to add more conditions:

- cell reference $E$2:$E$3 if you want more conditions
- the number after the second equal sign =2 to as many conditions you have in the formula
- {1; 1} to as many conditions you have. For example, 4 conditions - {1; 1; 1; 1}

### 5.1 How to create an array formula

### 5.2 Explaining formula in cell

#### Step 1 - Search for multiple strings

The SEARCH function allows you to find a string in a cell and it's character position. It also allows you to search for multiple strings in multiple cells if you arrange values in a way that works. That is why I use the TRANSPOSE function to transpose the values.

SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)

becomes

SEARCH(TRANSPOSE({"B";"f"}), $B$3:$B$13)

becomes

SEARCH({"B","f"}, $B$3:$B$13)

and returns

{#VALUE!, 1; 1, #VALUE!; 2, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; 1, #VALUE!; #VALUE!, 1; 1, 2; 2, #VALUE!; #VALUE!, 3; 3, 1}

#### Step 2 - Convert values into boolean values

The ISNUMBER function returns TRUE if value is number and FALSE for everything else including errors.

ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))

becomes

ISNUMBER({#VALUE!, 1; 1, #VALUE!; 2, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; 1, #VALUE!; #VALUE!, 1; 1, 2; 2, #VALUE!; #VALUE!, 3; 3, 1})

and returns

{FALSE, TRUE;TRUE, FALSE;TRUE, FALSE;FALSE, FALSE;FALSE, FALSE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE}

#### Step 3 - Convert boolean values

The MMULT function can't work with boolean values so we need to convert them to their numerical equivalents.

--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)))

becomes

--({FALSE, TRUE;TRUE, FALSE;TRUE, FALSE;FALSE, FALSE;FALSE, FALSE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE})

and returns

{0,1;1,0;1,0;0,0;0,0;1,0;0,1;1,1;1,0;0,1;1,1}.

#### Step 4 - Add numbers in array row-wise

MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3),$B$3:$B$13))),{1;1})=2

becomes

MMULT({0,1;1,0;1,0;0,0;0,0;1,0;0,1;1,1;1,0;0,1;1,1}, {1;1})=2

becomes

{1;1;1;0;0;1;1;2;1;1;2}=2

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}.

#### Step 5 - Prevent duplicates in the list

The next COUNTIF function counts values based on a condition or criteria, the first argument has this cell reference: $G$2:G2. It expands as you copy the cell and paste to cells below.

(COUNTIF($G$2:G2, $B$3:$B$13)=0)

becomes

{0;0;0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

#### Step 6 - Multiply arrays

We apply AND logic if we multiply the arrays, this means both values must be TRUE in order to return TRUE.

(COUNTIF($G$2:G2, $B$3:$B$13)=0)* (MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}

and returns

{0;0;0;0;0;0;0;1;0;0;1}

#### Step 6 - Divide 1 with array

The LOOKUP function ignores error values. Divide 1 with zero and we get #DIV/0! error.

1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)* (MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2))

becomes

1/{0;0;0;0;0;0;0;1;0;0;1}

and returns

{#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}.

#### Step 7 - Return values

LOOKUP(2, 1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)

becomes

LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}, $B$3:$B$13)

becomes

LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}, {"F"; "BAA"; "DBB"; "ADD"; "DAD"; "BDA"; "FDA"; "BFA"; "ABA"; "DAF"; "FDB"})

and returns "FDB" in cell G3.

**Get Excel *.xlsx file**

Search and display all cells that contain all search strings.xlsx

### Search and return multiple values category

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

### Excel categories

### 83 Responses to “Partial match and return multiple adjacent values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Can you do this by displaying an adjacent column instead of the column that was searched?

if that is not possible then can you do this formula by having List (A2:A12) start at A3 and go to A13?

okay I figured it out! thanks and I love your site!!

Man how long have you been working with excel?... i have just 2 years and I didn't have the slightest idea (till know ) you can do this only with formulas, I usually solve this kind of issues with VBA macros.

What can you recommend me to be able to do this? review each one of the formulas and its examples , or reading a lot of excel books or what?

I have learned a lot just by starting an excel blog.

Review others formulas and reading books is a good start. Enjoy what you are doing and solutions come easily into mind.

Oscar,

Thanks again. Would you help me with replaced search string1 with replace string1 and search string2 with replace string2....etc.... thanks,

James

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.

Great! although it's unable to correctly find the 02590 string. What could it be?

Andres,

02590?

Can you provide an example?

Hello Oscar,

My worksheet is failing not yours (blush). I put new strings on yours and it works just as I expected. Need to dig into it a bit more, he he.

Thanks for your valuable attention and congrats for your portal.

- Andres.

Hello Oscar,

Here I have an example (https://www.yourfilelink.com/get.php?fid=830240) for the functions of this page. As you can see in this example I am telling the spreadsheet to find what is in cell A3 but it show the content for row 3775 instead. I used hardcoded ranges aswell as named ranges with no change. When I put the content of the Example.xls spreadsheet in your spreadsheet it works but refuses to work in mine, perhaps due because I am using Excel 2003 but I am not fully convinced.

Thank you!

- Andres.

Andres,

Use this array formula in cell K7:

Hi Oscar,

I need to combine row and text columns into one "text" and suppress errors. Excel complaints that I exceeded the nested formulas.

Much appreciated..

Sorry Oscar,

Forgot to mention, only one search string is required, and may contain letters and numbers.

Best regards.

Carl,

combine row and text columns into one "text" and suppress errorsCan you provide an example?

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,

Get the 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,

Get the Excel *.xlsx file

Han-Hoe-Liw.xlsx

Read post:

Lookups in a related table (array formula)

I have two types of cells in the worksheet. Some are with green background and some are with plain white background. Some of these cells have a string of syntax in regular expression "ABC".

Each cell could have 1 or more strings according to regular expression above separated by ","(comma).

Could experts guide me on a formula

How to count the number of "ABC" in a every green/white cells to have an over all total in a given range.

For example, if cell 1 have 3 strings, cell 2 has 5 strings. The total is 8

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:

https://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 put 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:

https://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

SUMPRODUCT function

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

Check out the SUMPRODUCT function.

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

i have lookup array

A1 (anil) B1 (12)

A2(Singh) b2(13)

c1(Amit) c2(14)

looking for G1( Anil Singh Amit)

return value should be in G2(12 13 14)

if it possible kindly help me out

i have thousands of this type queries

if G1 ( Singh Anil Amit)

return value should be (13 12 14)

if G1 ( Singh Anil Amit)

return value should be (13 12 14)

if G1( Singh Anil Amit)

return value should be in G2(13 12 14)

Hi Oscar,

I have used the formula you have provided above

=INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(SEARCH($F$2, $B$3:$B$7)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7))), ROWS($A$1:A1)))

I have done this To search through a table of patients for if a procedure was aborted and why it was aborted. So it will spit out the reason a patient procedure was aborted. In some cases the procedure was aborted for the same reason. Using your formula the array is simply repeating the same reasons. Is there a way to modify the formula that if there is a repeat it will not add it as a row in my array. See what the formula spits out below:

1st Anchor Pull out

TSP Unsuccessful

TSP Unsuccessful

1st Anchor Pull out

1st Anchor Pull out

so it successfully searched for aborted in one column and then returned the adjacent row that had the reason in it. If the reason repeats I just would like it not to repeat the reason in my final array. Is this possible?

Thank you,

Rachel

Rachel,

Try these formulas:

Formula in cell H3:

=INDEX(C$3:C$8, SMALL(IF(ISNUMBER(SEARCH($F$3, $B$3:$B$8)), MATCH(ROW($B$3:$B$8), ROW($B$3:$B$8)), ""), ROWS($A$1:A1)))

Formula in cell I3:

=IFNA(INDEX($D$3:$D$8, MATCH(1, (COUNTIF($I$2:I2, $D$3:$D$8)=0)*($C$3:$C$8=H3), 0)), "")

Get the Excel *.xlsx file

Search-for-a-text-string-and-return-multiple-adjacent-values_Rachel.xlsx

G8 work bro, can we get the results in column wise rather then rows ??

It works horizontally as well. Use the same formulas as above but enter it in cell H2.

Then copy cell H2 and paste it to I2 and as far as needed.

=LOOKUP(2, 1/((COUNTIF(

$G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)The bolded cell reference above is important in order to get values horizontally: $G$2:G2

You need to change it so it points to the cell to the left of the current cell. Example, if you are going to enter the formula in cell L5 then change the cell reference to $K$5:K5.

Hi,

I have a list of movies into a column, and I want to list all cells that contains a specific word/s.

It is similar than doing a filter and looking for some chain "text", and I want to know and list all the cells that match that chain text.

Is that possible?

Joaquin,

The easiest way to list all cells containing "text" would be to apply a filter.

1. Select any cell in your data set.

2. Go to tab "Home".

3. Press with left mouse button on "Sort & Filter" button.

4. Press with left mouse button on "Filter".

The header names now have arrows.

1. Press with left mouse button on an arrow based on the column you want to filter.

2. Press with left mouse button on "Text Filters".

3. Press with left mouse button on "Contains..".

4. Type the text string you want to match.

5. Press with left mouse button on ok button.

The data is now filtered.

Sir,

I tried the excel file and it works well. but when I modify the number of rows I want to search from 3 to 10. it stops working. when the file is received the formula appears in {}, when I edit this goes away and the formula stops working.

Sir,

I opened the excel file it works well. but when I modify the number of rows I want to search from 3 to 10. it stops working. when the file is received the formula appears in {}, when I edit this goes away and the formula stops working. let me know how to solve it

Balachandra

The {} tells you that the formula is an array formula and not a regular formula. You need to enter the formula as an array formula if you edit an array formula.

1. Press and hold CTRL + SHIFT simultaneously.

2. Press Enter.

3. Release all keys.

split this into multiple rows

2 Unit Cattle Feed Supplement Vimicon 3 kg Rs. 750 10 Unit Drumstick PKM 1 50 gm Rs. 260 10 Unit Drumstick PKM 1 50 gm Rs. 260 Scheme price per pkt 12 rs discount 6 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 360 4 Unit Fodder Grass Alamdar 51 1 kg Rs. 675 4 Unit Fodder Grass Alamdar 51 1 kg Rs. 675 1 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 360

result should like this

2 Unit Cattle Feed Supplement Vimicon 3 kg Rs. 750

10 Unit Drumstick PKM 1 50 gm Rs. 260

10 Unit Drumstick PKM 1 50 gm Rs. 260 Scheme price per pkt 12 rs discount

6 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 360

4 Unit Fodder Grass Alamdar 51 1 kg Rs. 675

4 Unit Fodder Grass Alamdar 51 1 kg Rs. 675

1 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 360

Then it should assign a number specific word from a above eg vimicon assign a number 28 without using if condition from a table

Hello Oscar, can you provide Arrayformula on google sheets. I need to search numbers in one column against another then display values in an adjacent column. I can share my spreadsheet with you

Thanks for helping us !

I want to get similar data but only for the exact match of the cells,

say for eg.,

one

two

four

twenty one

five

thirty one

and I want to pull data corresponds to one only not for twenty one.

Can you please help on that ?

Manibala

Read this article: 5 easy ways to VLOOKUP and return multiple values

I need assistance with a search formula. I have leases with term periods and at the end of a term period in column J is an "X" I use this in formulas to review upcoming leases ending in 12 months on another tab. I want to return any and all renewal options available UNDER any row that contains the x (but from columns b, c, and d). I have over 50 leases on different tabs - so the placement of x indicating the term of the lease varies from tab to tab and also will change as leases are updated, but is always within a range of J10:J29.

This message is in response to the initial query raised by John Paul titled 'Search for a text string and return multiple adjacent values'.

My problem is trying to contain all the multiple returned values in one cell (separated by comma) rather than list the various returned values in vertical/horizontal adjacent cells.

I am able to obtain the first returned match by using the formula; =VLOOKUP("*"&Value&"*",Tab!$RangeA$,column number,FALSE)

However what I really require is all the returned values contained in one cell (separated by comma)... Any help would be gratefully appreciated.

Array formula:

TEXTJOIN, IF, ISNUMBER, and SEARCH functions.

Hi Oscar, your site is amazing!

Referring to "Search for a text string in multiple columns and return adjacent values"

Is there are way to only return the values which fulfill both search strings?

Thank you!

Yes, there is.

Excel 365 subscribers can use this smaller dynamic array formula:

It contains the new FILTER function that you can read about here: FILTER function

Is there a way to change TRANSPOSE($E$2:$E$3) from a column of data to a row?

I've tried $D$2:$E$2 and neither work in the cell but in the Function Arguments box it shows the expected value.

I think it is an issue with circular references but Excel can't show the problem.

Hi Oscar,

Thank you for posting these amazing tutorials! I am using this formula below to search for the subject I want to look up (let's say pens in cell K1). Then I also have many other things (erasers, rulers in cell L1, M1) to look up, so how can I apply this formula to the other ones? I tried applying this formula by dragging the right corner of the box, but because the formula is locked, the results will appear the same as for K1 for the L1 and M1 search. Is there a way to overcome this issue other than manually change the lookup cells in the search function?

=INDEX($A$1:$A$151,SMALL(IF(ISNUMBER(SEARCH($K$1,$C$1:$C$151)),MATCH(ROW($C$1:$C$151),ROW($C$1:$C$151))),ROW(A1)))