# Search for a text string in a data set and return multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values in a record. To filter records based on a condition read this: VLOOKUP - Extract multiple records based on a condition, that article also demonstrates how to filter records using the new FILTER function only available in Excel 365.

Example, the image above shows the data in cell range B3:E6, the condition is in cell C9. The formula extracts records from B3:E6 if at least one cell contains the string.

Row 3 is extracted above because string AA is found in cell C3. Row 4 and 5 are also extracted because string AA is found in cell D4 and E5 respectively. Row 6 is not extracted, there is no cell containing the search string.

Read section 1.1 for a detailed explanation of how this formula works. I have built a formula that matches two criteria and return multiple records.

### What is on this page?

- Search for a text string in a data set and return multiple records [Array formula]
- Search for a text string in a data set and return multiple records [Excel 365]
- Search for a text string in a data set and return multiple records [Excel defined Table]
- Search for a text string in a data set and return multiple records [Advanced Filter]
- Get Excel file
- Exact word in string

## 1. Search for a text string in a data set and return multiple records [Array formula]

This example demonstrates a formula that extracts records if any cell on the same row contains a specific value specified in cell C9.

This means also that the formula returns the same record multiple times if multiple cells contain the search value.

Array formula in B13:

To enter an array formula, type the formula in cell B13 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell B13 and paste to cell range B13:E16. Replace FIND function with SEARCH function if you don't want the formula to perform a case-sensitive search.

#### Search for a text string in a data set and return multiple records (no duplicates)

Array formula in cell B13:

The array formula above returns unique distinct records meaning no duplicate records if more than one cell matches the search string.

### 1.1 Explaining array formula in cell B13

#### Step 1 - Identify cells containing the search string

TheÂ FIND function returns the starting point of one text string within another text string, it returns a number representing the position of the found string. If not found the function returns the #VALUE! error.

FIND($C$9, $B$3:$E$6)

returns

{#VALUE!, 1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 4, #VALUE!; #VALUE!, #VALUE!, #VALUE!, 1; #VALUE!, #VALUE!, #VALUE!, #VALUE!}

The ISNUMBER function returns TRUE if the value in the array is a number and FALSE if not a number, it returns FALSE even if the value is an error value which is handy in this case.

ISNUMBER(FIND($C$9, $B$3:$E$6)) creates this array displayed in cell B11:E14:

Column B has no cells containing string "AA".

Column C has 1 cell containing string "AA". Cell C3

Column D has 1 cell containing string "AA". Cell D4.

Column E has 1 cell containing string "AA". Cell E5.

#### Step 2 - Return row number

We need to calculate the row number for each cell in order to replace TRUE in the array with the corresponding row number. To create the array we need we use the MATCH function and the ROW function.

MATCH(ROW(Table1), ROW(Table1)) returns this array displayed in cell range B11:E14:

If string "AA" is found in a cell in the table the corresponding row number is returned.

#### Step 3 - Replace boolean values with row numbers

TheÂ IF functionÂ has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(ISNUMBER(FIND($C$9, Table1)), MATCH(ROW(Table1), ROW(Table1)), "")

becomes

IF({#VALUE!, 1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 4, #VALUE!; #VALUE!, #VALUE!, #VALUE!, 1; #VALUE!, #VALUE!, #VALUE!, #VALUE!}, MATCH(ROW(Table1), ROW(Table1)), "")

becomes

IF({#VALUE!, 1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 4, #VALUE!; #VALUE!, #VALUE!, #VALUE!, 1; #VALUE!, #VALUE!, #VALUE!, #VALUE!}, {1; 2; 3; 4}, "")

and returns the following array shown in cell range B11:E14:

#### Step 4 - Sort the row numbers from smallest to largest

To be able to return a new value in a cell each I use theÂ SMALL functionÂ to filter column numbers from smallest to largest.

TheÂ ROWS functionÂ keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF(ISNUMBER(FIND($C$9, Table1)), MATCH(ROW(Table1), ROW(Table1)), ""), ROWS($A$1:A1))

returns 1.

#### Step 5 - Return a value at the intersection of a particular row and column

TheÂ INDEX functionÂ returns a value based on a cell reference and column/row numbers.

INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), MATCH(ROW(Table1), ROW(Table1)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX(Table1, 1, COLUMNS($A$1:A1))

becomes

INDEX(Table1, 1, 1)

and returns "East" in cell B13.

#### Step 6 - Remove errors

The IFERROR function allows you to display a blank if the formula returns an error.

## 2. Search for a text string in a data set and return multiple records [Excel 365]

The image above demonstrates an Excel 365 formula that extracts records based on a condition. The record is extracted if any cell in a record contains the condition.

Dynamic array formula in cell B13:

This formula works only in Excel 365, it returns an array of values that spills to cells below and to the right automatically. The formula contains the new FILTER function.

### 2.1 Explaining formula in cell B13

#### Step 1 - Identify cells containing the given search string

The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

FIND(C9, Table1)

becomes

FIND("AA", {"East", "AA BB", "II JJ", "PP QQ AA";"North", "CC DD", "KK AA", "RR SS";"South", "EE FF", "LL MM", "AA TT";"West", "GG HH", "NN OO", "UU VV"}))

and returns

{#VALUE!, 1, #VALUE!, 7;#VALUE!, #VALUE!, 4, #VALUE!;#VALUE!, #VALUE!, #VALUE!, 1;#VALUE!, #VALUE!, #VALUE!, #VALUE!}.

The FIND function returns a #VALUE! error if no string is found.

#### Step 2 - Check if value in array is a number

The ISNUMBER function returns a boolean value TRUE or FALSE. TRUE if the value is a number and FALSE for anything else, also an error value.

ISNUMBER(FIND(C9, Table1))

becomes

ISNUMBER({#VALUE!, 1, #VALUE!, 7;#VALUE!, #VALUE!, 4, #VALUE!;#VALUE!, #VALUE!, #VALUE!, 1;#VALUE!, #VALUE!, #VALUE!, #VALUE!})

and returns {FALSE, TRUE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE}.

#### Step 3 - Convert boolean values to numerical equivalents

The asterisk lets you multiply a value or array, this action converts boolean values to numbers automatically. This step is required because the MMULT function can't work with boolean values.

ISNUMBER(FIND(C9, Table1))*1

becomes

{FALSE, TRUE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE} * 1

and returns

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

#### Step 4 - Create a number sequence

The ROW function calculates the row number of a cell reference.

ROW(*ref*)

ROW(Table1)

returnsÂ {3; 4; 5; 6}.

#### Step 5 - Change numbers to 1

The power of or exponent character is able to convert each number if number to the power of zero is calculated.

ROW(Table1)^0

becomes

{3; 4; 5; 6}^0

and returns {1; 1; 1; 1}.

#### Step 6 - Consolidate numbers

The MMULT function calculatesÂ the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(*array1*,Â *array2*)

MMULT(ISNUMBER(FIND(C9, Table1))*1, ROW(Table1)^0)

becomes

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

and returns {2; 1; 1; 0}.

#### Step 7 - Filter values based on array

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(*array*,Â *include*, [*if_empty*])

FILTER(Table1, MMULT(ISNUMBER(FIND(C9, Table1))*1, ROW(Table1)^0))

becomes

FILTER(Table1, {2; 1; 1; 0})

and returns the following array in cell B13:

{"East", "AA BB", "II JJ", "PP QQ AA"; "North", "CC DD", "KK AA", "RR SS"; "South", "EE FF", "LL MM", "AA TT"}

## 3. Search for a text string in a data set and return multiple records - Excel Table

This example demonstrates how to filter records if any of the cells on a row contains a specific string using an Excel Table. You need a formula and a helper column to accomplish this task.

You can't do this using the "Custom Autofilter" built-in to the Excel Table, there is no way to use OR logic between filters across columns, you need a formula to do this. As far as I know.

### 3.1 Convert dataset to an Excel defined Table

- Select any cell within the dataset.
- Press CTRL + T

- Press with left mouse button on checkbox if your dataset contains headers for each column.
- Press with left mouse button on OK button.

### 3.2 Add formula to Excel defined Table

- Select cell F3.
- Type formula: =COUNTIF(Table13[@[January]:[March]],"*AA*")
- Press Enter.

Excel fills the remaining cells in the table for you and creates a header name for your new column automatically.

### 3.3 Explaining formula in cell F3

#### Step 1 - COUNTIF function

The COUNTIF function calculates the number of cells that is equal to a condition.

COUNTIF(*range*,Â *criteria*)

#### Step 2 - Populate arguments

COUNTIF(Table13[@[January]:[March]],"*AA*")

*range* - Table13[@[January]:[March]] is a structured reference to data in columns January to March in table *Table13*. The at sign @ before the header names indicate that the reference is to values on the same row.

*criteria - *"*AA*" The asterisk character is a wildcard character that matches 0 (zero) to any number of characters. When we use a leading and trailing asterisk the criteria matches cells that contain "AA".

#### Step 3 - Evaluate formula

COUNTIF(Table13[@[January]:[March]],"*AA*")

becomes

COUNTIF({"AA BB", "II JJ", "PP QQ"}, "*AA*")

and returns 1 in cell F3. String AA was found once in cell range C3:E3. Note that the formula evaluates only cells on the same row, this is why the formula doesn't return an array of values.

### 3.4 Filter Excel Table

To filter the records containing string AA at least once follow these steps:

- Press with left mouse button on black arrow next to the header name "April".

- Press with left mouse button on checkbox next to 0 (zero) to deselect it.
- Press with left mouse button on OK button.

April is not the correct header name, I changed it to Condition.

## 4. Search for a text string in a data set and return multiple records [Advanced Filter]

The Advanced Filter is a powerful feature in Excel that allows you to perform OR-logic between columns. The asterisk lets you do a wildcard lookup meaning that a record is filtered if the text string is found somewhere in the cell value.

### 4.1 Add columns

- Copy column headers and paste to cells above or below the dataset. Note, if you place them next to the dataset they may become hidden when the filter is applied.
- Type the search condition and add an asterisk before and after the text string.
- Add another search condition, make sure they are in a row each in order to perform OR-logic.
- Repeat with the remaining criteria.

### 4.2 Apply filter

- Select the dataset.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on the "Advanced" button.

- Press with left mouse button on the radio button "Filter the list, in-place".
- Select Criteria range:"
- Press with left mouse button on "OK button.

To delete the filter applied simply select a cell within the filtered dataset, then go to tab "Data" on the ribbon and press with left mouse button on "Clear" button.

## 5. Excel file

**How do I extract rows that contain a string in a data set or table?**

You can use a formula to extract records based on a search value, it also returns multiple records if there are any that match. The advantage of using a formula is that it is dynamic meaning the result changes as soon as a new search value is entered. The downside with the formula is that it may become slow if you have lots of data to work with.

**How do I filter rows that contain a string using Advanced Filter?**

You also have the option to filter records using an Advanced Filter, it allows you to perform multiple search values using OR-logicÂ across multiple columns. This article explains how to set it up, jeep in mind that it needs a small amount of manual work in order to apply new filters.

**How do I filter rows that contain a string using an Excel defined Table?**

The Excel defined Table needs the COUNTIF function to accomplish the task which may slow down the calculation considerably if your data set is huge. I recommend using the Advanced Filter if speed is an issue.

## 6. Exact word in string

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me to create an excel formula doing the same thing as the vba function.

In short, from the above blog post

*The function,Â modified the first argument (Text) and replaces all non-alpha characters with a space character. It then adds a leading and trailing space to both arguments. Finally, it uses the Instr function to determine if the modified Word argument is present in the modified Text argument*

*The ExactWordInString function looks for a complete word -- not text that might be **part of a different word. *

**Edit: Read Rick Rothstein (MVP - Excel) comments at the bottom of this blog post.**

Here is the formula I created in C2:

copied down as far as necessary.

### Explaining formula in cell C2

#### Step 1 - Check if stringÂ exists in cell

The FIND function returns the positionÂ of a string in cell value, if not found an error is returned. The ISNUMBER function returns TRUE for all numerical values and FALSE for all else even error values.

The IF function uses the boolean value to determine if the first argument or second argument is going to be returned.

IF(ISNUMBER(FIND(B2, A2)), *formula*, FALSE)

becomes

IF(ISNUMBER(FIND("trap", "Million of innocent rats are trapped--quartely!")), *formula*, FALSE)

becomes

IF(ISNUMBER(30), *formula*, FALSE)

becomes

IF(TRUE, *formula*, FALSE)

The IF function now continues the calculation with the first argumentÂ *formula.*

#### Step 2 - Check if the character before the string is larger than ansi code 122

The CODE function converts a character into ansi equivalent. Character "A" is 65 and "z" is 122.

COUNT((IF(CODE(MID(A2,FIND(B2,A2)-1,1))>122,1,""))

becomes

COUNT((IF(CODE(MID(A2,FIND(("trap","Million of innocent rats are trapped--quartely!")-1,1))>122,1,""))

becomes

COUNT((IF(CODE(MID(A2,30-1,1))>122,1,""))

becomes

COUNT((IF(CODE(" ")>122,1,""))

becomes

COUNT((IF(32>122,1,""))

becomes

COUNT((IF(FALSE,1,""))

becomes

COUNT(("")

and returns 0.

#### Step 3 -Â Check if character before the string is less than ansi code 65

The MID function extracts a part of a string based on a start character and the length.

IF(CODE(MID(A2,FIND(B2,A2)-1,1))<65,1,"")

becomes

IF(CODE(MID(A2,30-1,1))<65,1,"")

becomes

IF(CODE(" ")<65,1,"")

becomes

IF(32<65,1,"")

and returns 1.

#### Step 4 - Check if string is at the very beginning

IF(FIND(B2,A2)-1<1,1,"")

becomes

IF(30-1<1,1,"")

becomes

IF(FALSE,1,"")

and returns "".

#### Step 5 - Check if the character after the string is larger than ansi code 122

The LEN function counts characters in a cell.

COUNT((IF(CODE(MID(A2,FIND(B2,A2)+LEN(B2),1))>122,1,""))

becomes

COUNT((IF(CODE("p")>122,1,""))

becomes

COUNT((IF(112>122,1,""))

becomes

COUNT((IF(112>122,1,""))

becomes

COUNT("")

and returns 0.

#### Step 6 -Â Check if character after the string is less than ansi code 65

The IF function returns a value determined by the logical expression in the first argument. If TRUE then the second argument is returned, FALSE returns the third argument.

IF(CODE(MID(A2,FIND(B2,A2)+LEN(B2),1))<65,1,"")

becomes

IF(CODE("p")<65,1,"")

becomes

IF(112<65,1,"")

and returns "".

#### Step 7 -Â Check if string is at the very end

IF((FIND(B2,A2)+LEN(B2)+1)>LEN(A2),1,"")

becomes

IF(35>LEN(A2),1,"")

becomes

IF(35>47,1,"")

and returns "".

#### Step 8 - Nested IFs

All these IF functions are nested.

#### Get Excel *.xlsx file

Exact word in string using excel functions.xlsx

### Filter records category

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Lookup with criteria and return records.

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

### Excel categories

### 78 Responses to “Search for a text string in a data set and return multiple records”

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

Thank you so much for this formula! You helped me so much. Please don't stop helping others. You certainly have a gift. God Bless!

Thanks Michael!

There seems to be an error in the above formula. If cell A11 contains the text value Million and B11 contains the text value Millio the formula returns TRUE.

(Thanks Niranjan)

Here is a formula that produces the same output as the one you posted but which uses less than half the number of function calls, is almost half the size (length-wise) and which produces the same output...

=AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH(B2, A2), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", FIND(UPPER(B2), UPPER(A2))+LEN(B2)+1, 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

Note: I used the FIND/UPPER construction on purpose; if you used SEARCH then "exact words" lying next to an asterisk or question mark could make the formula produce incorrect results because SEARCH would assume they were wildcards.

By the way, if you missed it, I think you might be interested in the update John posted to his blog entry showing the one-liner VB function that I sent him which does the same thing as his 12-line function does.

CORRECTION...

Damn, I posted the wrong formula (it is missing the IF function housing. Here is the correct formula...

=IF(ISNUMBER(SEARCH(B2, A2)), AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH(B2, A2), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))), NOT(ISNUMBER(FIND(UPPER(MID("="&A2&"=", SEARCH(B2, A2)+LEN(B2)+1, 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))

The comparison numbers change with this (one, because I had left some function calls out, but two, because I left the separating spaces in the function you posted)... my formula is about two-thirds the length of yours (215 characters versus 318 characters) and it uses just a little more than half the function calls (17 versus 31).

Amazing! Your formula works perfectly! Thank you for commenting!

You are quite welcome. When I first saw the question in John Walkenbach's blog, he had posted a VBA solution so I gave him back my one-liner VBA solution as a response. It did not even occur to me to try and solve the problem via a worksheet formula. When I saw your posted formula solution, it prompted me to try my hand at one as well. It was a fun exercise, so I thank you for thinking of it.

Now, back to your "Your formula works perfectly" comment. I just want to point out one difference between my formula and yours... as written, my formula is case **insensitive** where as yours is not. Walkenbach's original code was case insensitive, so I made my response to him the same... and it made sense to me for it to be case insensitive to cover the possibility that the word might be located at the beginning of a sentence. However, if you want to change my formula to be case sensitive, you can do that by simply changing the very first SEARCH function call to a FIND function call (leaving the rest of the formula alone).

I would like to use the "original" formula which is used in the example file. However it's case sensitive and I need it case insensitive as some of the words I'm looking for is the first letter and therefore Capital letter. Can you help with that.

Hi, this is a great formula. How can it be changed to detect whole numbers? Example String:

132, 98, 198, 200 - 222 AXT 78

Where if I search for 98 it returns TRUE but 22 returns FALSE.

Thanks for any help!

@Mario,

It is unclear if you are referring to my formula or the formula that Oscar included in his original blog article. Assuming you meant my function, I believe this modification of it will work correct to search for the specified number (and ONLY for numbers, not non-digits, even if coupled with digits) within a string of characters...

=IF(ISNUMBER(SEARCH(B2, A2)), AND(NOT(ISNUMBER(FIND(MID("="&A2&"=", SEARCH(B2, A2), 1), "0123456789"))), NOT(ISNUMBER(FIND(MID("="&A2&"=", SEARCH(B2, A2)+LEN(B2)+1, 1), "0123456789")))))

Rick Rothstein - you have seriously saved my day.

@Mattias Olsson,

I'm not sure which of my two formulas you were referring to, unless you meant my macro (which I alluded to in my first response to Oscar) that John Walkenbach posted in his blog; but I just wanted to say that you are quite welcome, I am glad that something I posted turned out to be useful to you.

Thank you all for commenting!

Oscar, do you know of any macro or free files to calculate the processing speed of Excel formulas or functions?

I know you can use "Timer" to calculate user defined functions and macros. John Walkenbach has an example on his blog: How Fast Is Your System?

gr8..really thanksful Rick Rothstein

@tricky...

Thanks for commenting on my formulas; I am glad you found them useful to you in some way. If you are into VB code, you should check out my response to John Walkenbach... my one-liner VB code is even more compact than the formula version. See the *UPDATE* section on this webpage...

https://spreadsheetpage.com/index.php/tip/is_a_particular_word_contained_in_a_text_sring/

Dear Oscar,

Do you know if there is a formula (or combination) that results in a text value I want to use with "vlookup"

Something like the "like" function in Access.

Example:

text = "Kn1263-Techstore-MrFixit-12-11-2011"

I want to Find the part "Tech".

Then with Vlookup I want to find a related account in an Table.

such a Tech is related to "200 Repare account"

So I can keep controle over my Bankaccount for example.

Maybe in VBA?

Thanks in advance for your reply.

Kind regards,

Lourens van 't Wout

The Netherlands

Lourens van 't Wout,

Yes, I believe it is possible.

Can you describe the two tables and how they are related?

Lourens,

This post describes how to search for multiple textstrings and return a match from another table: https://www.get-digital-help.com/2010/11/22/return-multiple-matches-with-wildcard-vlookup-in-excel/

Hi Oscar,

Request your help for a similar kind of a query. I'm in a fix and can't really figure out how to get the required output.

I have two columns A & B both containing string values. The value in Column A is basically a reference number and value in Column B contains detailed text containing that number itself...

For e.g. Row 1 for Column A has a value 00125465.. Now in column B I have a value like (without qoutes) "With reference to the record number 00125465, we would like to..". This value of column A might exist in multiple rows of Column B.

In my actual data there are around 160,000 unique values for column A and 97,000 values for column B. I need to search for all values of column A in column B and have some kind of an identifier in say Column C to know what values for column A are present in Column B. Please note that a value for Column A might exist on row # 10 and then row # 1000 in column B.

Hope you can help me with this.

Cheers !

Hasan,

Check out the attached file:

Hasan.xlsx

Hi Oscar,

Many thanks for the file. Apparently it looks to be exactly what I was looking for. This is simply great :)..

Having said that the code seems to be really complex. Can I please have your email address so that I can request for any clarifications if required for the code ?

Once again thanks a lot.

God Bless !!

Hi Oscar,

Sorry for bothering you again for this. Will you be please kind enough to define the formula in two three lines in simple words for me.. Can't really understand how these functions are working.. specially ' _xlfn.IFERROR ' :(..

Apologies for the bother but would really appreciate your help.

God Bless !

Hasan,

You can find an explanation here:

https://www.get-digital-help.com/2010/11/22/return-multiple-matches-with-wildcard-vlookup-in-excel/

IFERROR function removes errors.

hi, i was wondering how can i make this search formula to work in excel 2003 i get an error #name! in the search result box. thanks

ali,

Array formula:

=INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)

search-for-a-string-in-an-excel-table.xlsx

ple seam file find transfar to sheet no 2

Hello Oscar,

If I do not want to use table in the formular for ranges in 2003?

My search is in range a1:a20 and its looking for the text in b2:b500

Array formula:

=INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)

James,

I believe this post answers your question:

https://www.get-digital-help.com/2010/04/07/lookup-values-in-a-range-using-two-or-more-criteria-and-return-multiple-matches-in-excel-part-2/

Array formula:

Hi Oscar,

This formula works great for what I'm trying to achieve.

I have a project data sheet capturing names of people involved. I'm using this to allow people to search for a particular name, and for all relevant projects to be displayed.

Have modified the formula to use a SEARCH function instead of FIND for the closest match, but I need to identify all the column numbers in the data matrix where there's a match for an index function against another matrix of the same size.

I've figured out how to retrieve the row numbers, but am stuck at getting the right columns to be displayed.

Wenyong,

but I need to identify all the column numbers in the data matrix where there's a match for an index function against another matrix of the same size.Can you explain in greater detail?

Thanks Oscar, for the prompt reply.

Is it possible for me to attach or send you an example of what I'm working on?

I'll try explaining in more detail:

I have a datasheet on different projects, names of colleagues involved in the project are organized in 12 columns (each cell may contain more than one name. For this reason, I've modified your function to use SEARCH instead of FIND), and their involvement in the project captured in another array of 12 columns.

Purpose of the spreadsheet is for any user to enter a search name (e.g. Peter) and for the spreadsheet to display all projects this person is involved in. For this, the function you've developed works beautifully. This is also why I'm using SEARCH, as the user may enter only a first name, and the function can capture the closest match from a cell that may contain 3 names. FIND can only return an exact match.

Besides returning the name of the project as a result, I'm looking to develop this further by displaying the role of the person for this project. For example, Peter is involved in Project X as a Sales Manager. His name is captured in Resource_Name_Column7 and his role is captured in Role_Column7.

The current function can return Project X as a result when user searches for 'Peter'. However, I do not want to index out all 12 columns that matches the Row at which his name is found.

I'd like to display the role he plays in this project by indexing the x and y coordinates at which his name is found in the Names array, against the Roles array which is of the same size (both 12 columns).

I've manage to display and return the result for Row number of the project, but couldn't find a way to capture the column number at which his name is found

Wenyong,

You can use this contact form.

The above example is excellent one.

But my requirement is something different.

I need to print only column1 contents when the pattern AA matches.

Also if "AA" presents twice in same row this particular cloumn1

content should be repeated twice.

Thanks

senthil,

Read this: Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values

Oscar - Thanks so much for providing this! It's really helpful.

I've implemented the array formula and it works great except that if the search string occurs 2x in row of data, the row is returned twice. If it occures 3x, the row is returned three times.

I'd like the results to show only 1 instance of each row no matter how many times the search string appears in that row. How can that be done?

Thx!

Richard,

See this file:

search-for-a-string-in-an-excel-table-return-unique-rows.xlsx

Thanks, Oscar. The function as is in the file was returning the same text string in all cells in the array - and not the search string. I changed the end part of the function from "...ROW(A1)),COLUMN(A1)),"")" to "...ROW(Table1)),COLUMN(Table1)),"") and it is working... mostly. I'm no longer getting duplicate rows. However, the function is failing to return some rows that include the text string. I can't quite figure out what is unique about the rows returned vs those that should be and are not (or vis-versa).

What do you think the fix is?

Thx!

Richard,

Thanks, Oscar. The function as is in the file was returning the same text string in all cells in the array - and not the search string. I changed the end part of the function from "...ROW(A1)),COLUMN(A1)),"")" to "...ROW(Table1)),COLUMN(Table1)),"")However, the function is failing to return some rows that include the text string. I can't quite figure out what is unique about the rows returned vs those that should be and are not (or vis-versa).

You have entered the same array formula in all cells. Enter the formula in the first cell and then copy the cell (not the formula) to the right. Copy the entire row and paste down.

The array formula contains relative cell references. They change when you copy the formula.

HELLO,

i have a column with many rows containing select queries.

e.g

query_column

select * from abc where ...

select * from efgh where ...

select * from pqrst where .....

i want output in next column as

abc

efgh

pqrst

how can this be done?

please help.

Hey, I want to use one command, i searched in but i didn't found. please help if you have the solution.

My question is: In a EXCEL 2007 work book I want to find the cells only containing the word (STRING). for example:

refuse 6 to 2 accept 6 this 4 responsibility 14 which 5 the 3 god 3 of 2 the 3 universe 8

blade 5 are 3 pressed 7 against 7 the 3 upper 5 grinders. 8 ) 1 what 4 shall 5 our 3

the 3 passage 7 of 2 air. 3 ) 1 the 3 aspirate 8 quality, 7 or 2 whisper, 7 is 2

alone 5 in 2 the 3 room, 4 you 3 will 4 sound 5 very 4 silly 5 if 2 you 3

you 3 if 2 he 2 11 let 3 me 2 see 3 his 3 programme, d. 1 hark

me 2 recommend that 4 of 2 the 3 effective 9 speaking 8 voice 5 which 5 deals with 4

the 3 death 5 of 2 molly cass, the 3 little 6 cripple s 1 garden, the 3

laugh never 5 does 4 anyone any 3 harm I 1 those 5 dangerously dynamic 7 british

offensively healthy 7 english 7 families, 8 ever 4 since 5 mr. 2 britling we 2 have 4 been 4

grandmothers, who 3 all 3 get 3 out 3 at 2 in 2 the 3 morning and 3 play 4

which 5 the 3 sisters until 5 they 4 begin to 2 have 4 children along 5 in 2

this is my work book, only i copied a part of the workbook. the words are with numbers. I want to find / search a word which is not with a number (the word is what ever it may be / random word).

please help me.

JAFAR,

I donÂ´t understand, can you upload a picture of our worksheet?

Upload picture to postimage.org

The File Image is not uploading. I will copy and paste here. In this down side file the some cells contains both number and text, and some cells contains only the text. In this work book I want to find the text containing cells. Please help me.

ESTABLISH 9 A 1 HOME 4 RAISE 5

all 3 about 5 your 4 own 3 mental 6 capacity, 8 from 4 a 1 hasty 5 glance 6 through 7 the 3 various 7 tests 5 i-figure 7 it 2 out 3 that 4 i 1 would 5 be 2 classified 10 in 2 group 5 b, 1 indicating 10 low 3 average 7 ability, 7 reserved 8 usually 7 for 3 those 5 just 4 learning 8

to 2 speak 5 the 3 english 7 language 8 and 3 preparing 9 for 3 a 1 career 6 of 2 holding 7 a 1 spike 5 while 5 another 7 man 3 hits 4 it. 2 if 2 they 4 ever 4 adopt 5 the 3 menti 5 meter 5 tests 5 on 2 thisjournal 11 i 1 shall 5 lastjust 8 about 5 #NAME? minutes, 7 and 3 the 3

trouble 7 is 2 that 4 each 4 test 4 starts 6 off 3 so 2 easily, 6 you 3 begin 5 to 2 think 5 that 4 you 3 are 3 so 2 good 4 that 4 no 2 has 3 ever 4 appreciated 11 you. 3 there 5 is 2 for 3 instance, 8 a 1 series 6 of 2 twenty 6 pictures 8 (very badly 5 drawn 5 too, 3 mr. 2 frank 5

parker 6 stock 5 bridge. 6 you 3 think 5 you 3 are 3 so 2 smart, 5 picking 7 fiaws 5 with 4 people 6 s 1 intelligence, 12 if 2 i 1 couldn 6 t 1 draw 4 a 1 better 6 head 4 than 4 the 3 on 2 page 4 i 1 would 5 throw 5 up 2 the 3 whole 5 business), at 2 any 3 rate, 4 in 2 each 4 of 2

these 5 pictures 8 there 5 is 2 something 9 wrong 5 (wholly a 1 from 4 the 3 drawing), you 3 are 3 supposed 8 to 2 pick 4 out 3 the 3 incongruous 11 feature, 7 and 3 you 3 have 4 seconds 7 in 2 which 5 to 2 tear 4 the 3 twenty 6 pictures 8 to 2 pieces, 6 the 3 first 5

is 2 easy, 4 the 3 rabbit 6 has 3 human 5 ear. 3 in 2 unpardonable sin of 2 hitting the 3 nail on 2 the 3 head, 4 he 2 might 5 almost 6 have 4 seen 4 an 2 advance copy of 2 the 3 honours 7 list, tranto. 6 he 2 hadn 4 t. 1 nor 3 had 3 who 3 s 1 in 2 it 2 culver, 6 you 3

might 5 ask 3 who 3 isn 3 t 1 in 2 it. 2 (taking a 1 paper 5 from 4 his 3 pocket.) well, 4 gentleties in 2 it. 2 he 2 gets 4 a 1 knighthood, tranto. 6 never 5 heard 5 of 2 him 3 who 3 is 2 he 2 hiidegarde. oh, 2 yes, 3 you 3 ve 2 heard 5 of 2 him. 3 (John glances at 2

her 3 severely.) he 2 s 1 m.p. for 3 some 4 earthly paradise or 2 other 5 in 2 the 3 south riding, tranto. 6 oh 2 culver, 6 perhaps 7 i 1 might 5 read 4 you 3 something 9 writ 4 by 2 my 2 private 7 secretary 9 he 2 s 1 of 2 these 5 literary 8 wags, you 3 see 3

there 5 s 1 been 4 a 1 demand that 4 the 3 government 10 should 6 state 5 clearly, in 2 every 5 case 4 of 2 an 2 honour, 6 exactly 7 what 4 services the 3 honour is 2 given 5 for. 3 this 4 (taking paper 5 from 4 his 3 pocket) is 2 supposed 8 to 2 be 2 the 3 stuff 5

sent 4 round 5 to 2 the 3 press 5 by 2 the 3 press 5 bureau, (reads.) mr. 2 gentletie has 3 gradually 9 made 4 a 1 solid 5 reputation #NAME? himself 7 as 2 the 3 dullest man 3 in 2 the 3 house 5 of 2 commons, whenever 8 he 2 rises to 2 his 3 feet 4 the 3

house 5 empties as 2 if 2 by 2 magic, in 2 cases of 2 inconvenience, when 4 the 3 government 10 wishes abruptly to 2 close 5 a 1 debate 6 by 2 counting 8 out 3 the 3 house, 5 it 2 has 3 invariably 10 put 3 up 2 mr. 2 gentletie to 2 speak, the 3 device has 3

never 5 been 4 known 5 to 2 fail, nobody 6 can 3 doubt 5 that 4 mr. 2 gentletie s 1 patriotic devotion to 2 the 3 allied cause 5 well 4 merits the 3 knighthood which 5 is 2 now 3 bestowed on 2 him. 3 John 4 (astounded.) stay 4 me 2 with 4 ftagons

tranto. 6 so 2 that 4 s 1 that 4 and 3 who 3 else 4 culver, 6 another 7 of 2 your 4 esteemed uncles, 6 tranto. 6 well, 4 that 4 s 1 not 3 very 4 startling, seeing that 4 my 2 uncle 5 s 1 chief 5 daily 5 organ 5 is 2 really 6 a 1 de 2 ment 4 of 2 the 3 government. John, 4

what 4 isay is 2 hiidegarde (simultaneously with 4 John), wouldn 6 t 1 it 2 be 2 more 4 correct (continuing alone 5 ) 1 wouldn 6 t 1 it 2 be 2 more 4 correct to 2 say 3 that 4 the 3 government 10 is 2 really 6 a 1 de 2 ment 4 of 2 your 4 uncle 5 s 1 chief 5 daily 5

organJohn, hilda, old 3 girl, 4 i 1 wish 4 you 3 wouldn 6 t 1 interrupt, cookery 7 s 1 your 4 line, 4 hiidegarde. sorry,Johnnie, isee 4 i 1 was 3 in 2 danger of 2 becoming 8 unsexed. culver 6 (tojohn). yes 3 you 3 were 4 about 5 to 2 say 3 John, 4 oh, 2

nothing, 7 culver 6 (to tranto). shall 5 iread thepassage on 2 your 4 uncle 5 tranto. 6 don 3 t 1 trouble, who 3 S the 3 next 4 culver, 6 the 3 next 4 is 2 ullivant, munitions manufacturer, let 3 me 2 see. 3 (reads.) by 2 the 3 simple 6 means 5 of 2

front 5 of 2 the 3 book 4 for 3 the 3 use 3 of 2 this 4 poem, 4 and 3 only 4 rightly 7 too, 3 for 3 without 7 it 2 the 3 story 5 could 5 never 5 have 4 been 4 writ), he 2 goes 4 out 3 into 4 the 3 ocean, 5 but 3 there 5 we 2 mustn 5 t 1 give 4 too 3 much 4 of 2 the 3 plot 4 away, 4

all 3 that 4 need 4 know 4 is 2 that 4 luke 4 or 2 sir 3 nigel, 5 as 2 you 3 wish 4 (and what 4 reader 6 offlorence 10 ba 2 relay 5 wouldn 6 t 1 prefer 6 sir 3 nigel?), was 3 so 2 cultured 8 that 4 he 2 said, 4 nobody 6 in 2 the 3 whole 5 world 5 knows 5 it, 2 save 4 you 3 and 3 i, 1

and 3 referred 8 to 2 fiotsam 7 andjetson 9 as 2 he 2 was 3 swimming 8 out 3 into 4 the 3 path 4 of 2 the 3 rising 6 sun. 3 jetsam 6 is 2 such 4 an 2 ugly 4 word, 4 it 2 is 2 only 4 fitting 7 that 4 on 2 his 3 tombstone 9 lady 4 tintagel 8 should 6 have 4 had 3 inscribed 9 an 2

impressive 10 and 3 high 4 sounding 8 misquotation 12 from 4 the 3 bible. 5 I 1 measure 7 your 4 mind 4 measure 7 your 4 mind 4 by 2 m.r. 2 traube 6 and 3 frank 5 parker 6 stockbridge, 11 is 2 apt 3 to 2 be 2 a 1 very 4 discouraging 12 book 4 if 2 you 3 have 4 any 3 doubt 5

at 2 saying 6 that 4 the 3 cost price 5 of 2 shells was 3 een shillings 9 and 3 pence each, 4 whereas 7 it 2 was 3 in 2 fact 4 only 4 shillings 9 and 3 pence, mr. 2 Joshua ullivant has 3 made 4 a 1 fortune of 2 million 7 pounds during 6 the 3 war. 3 he 2 has 3

given 5 a 1 hundred 7 thousand 8 to 2 the 3 prince of 2 wales s 1 fund, a 1 hundred 7 thousand 8 to 2 the 3 red 3 cross, 5 and 3 a 1 hundred 7 thousand 8 to 2 they 4 funds, total net profit 6 on 2 the 3 war, 3 million 7 hundred 7 thousand 8 pounds, 6 not 3

counting 8 the 3 peerage which 5 is 2 now 3 bestowed upon 4 him, 3 and 3 which 5 it 2 must 4 be 2 admitted 8 is 2 ajust reward 6 for 3 his 3 remarkable business 8 acumen, tranto. 6 very 4 agreeable 9 fellow 6 ullivant is, 2 nevertheless, culver, 6 oh, 2

he 2 is. 2 they 4 re 2 most 4 of 2 them 4 too 3 damned 6 agreeable 9 for 3 anything, another 7 prominent name 4 is 2 orlando bush, tranto. 6 ah 2 mrs. 3 culver, 6 ive 3 met 3 his 3 wife, 4 she 3 dances beautifully at 2 charity matinees, culver, 6 no 2

doubt, 5 but 3 apparently that 4 s 1 not 3 the 3 reason, 6 tranto. 6 i 1 know 4 orlando. ive 3 Just bought 6 the 3 serial rights of 2 his 3 book, 4 culver, 6 have 4 you 3 pa 2 id 2 him 3 tranto. 6 no. 2 culver, 6 how 3 wise of 2 you 3 (reads ). 1 mr. 2 orlando

bush has 3 writ 4 a 1 historical sketch, with 4 many 4 circumstantial details, of 2 the 3 political 9 origins of 2 the 3 present 7 government, fir 3 his 3 forbearance in 2 kindly consenting to 2 with 4 old 3 publication until 5 the 3 end 3 of 2 the 3 war 3

mr. 2 bush receives a 1 well 4 earned tranto. 6 what 4 culver, 6 knighthood, tranto. 6 cheap but 3 what 4 a 1 sell 4 for 3 me 2 culver, 6 now, 3 ladies 6 and 3 gentlemen, the 3 last 4 name 4 with 4 which 5 i 1 will 4 trouble 7 you 3 is 2 that 4 of 2 mr. 2 James

brill, tranto. 6 notjimmy brill the 3 second 6 the 3 woman 5 s 1 eye 3 is 2 in 2 her 3 hair, 4 pretty 6 soft, 4 you 3 say 3 to 2 yourself 8 in 2 the 3 third 5 the 3 bird 4 has 3 legs, 4 it 2 looks 5 like 4 a 1 cinch, 5 following 9 in 2 quick 5 succession 10 come 4 a 1 man 3 with 4

his 3 mouth 5 in 2 his 3 forehead, 8 a 1 horse 5 with 4 cows 4 horns, 5 a 1 mouse 5 with 4 rabbit 6 s 1 ears, 4 etc. 3 you 3 will 4 have 4 time 4 for 3 a 1 handspring 10 before 6 your 4 seconds 7 are 3 up. 2 but 3 then 4 they 4 get 3 tricky, 6 there 5 is 2 a 1 post 4 card 4

CONFIDENSIAL 12 324

JAFAR,

Have you read this post:

Search for multiple text strings in multiple cells

Thank you very much. The function works perfectly, but only is case the letter before or after the "exact word" is that of the English ABC. In cases where a special Hungarian letter (like "Ã¶", "Ãº") stands right after the word, it says "TRUE", not "FALSE". Can you help me?

I guess it has something to do with the "code" part of the function where the numbers indicate the position of the code number of the letters in the character table, don't they? Sorry for my silly questions.

SOLVED. With Rick Rothstein's function (thanks a lot!!!).

I realised it did not work for the first time because I did not pay attention to the extra spaces and the commas in the function. Now it works. All I had to do was translating the function elements, deleting the spaces, changing the commas to semicolons and adding the extra letters of the Hungarian alphabet. Thanks again!!!

Is there a way to set something like this up with a SUMIF formula? I need to add up revenue related to a list of companies. I don't want Kia's revenue to incluce Nokia's! This has to be done through referencing cells.

Pablo,

if there is a blank before and after the search string, you can use this formula:

Thank you Oscar! I wish it was that easy. The company names are placed in many different positions. If a cell only has the word Kia in it, or if it is in the beginning, then it wouldn't be recognized with that formula.

I have found two solutions! Happy to share with you:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&G2&" "," "&$B$2:$B$10&" ")),$C$2:$C$10)

G2 = Key word

$B$2:$B$10 = List of companies

$C$2:$C$10 = Revenue list

Someone else posted: I assume you are looking for "Ford" which is case-sensitive, right? So that you won't have something like "afFordable health care", "AshFord" or even "Texas ford" in your list. If that's the case, you may try:

=SUM(IF(ISNUMBER(FIND(G2,$B$2:$B$417)),$C$2:$C$417,0))

CTRL SHIFT ENTER

Pablo,

thank you for posting solutions. Very appreciated!

Re: Exact word in string without VBA. - Referring to first solution posted

Thanks so much but I found a bug in your code. I was comparing the contents of two cells as originally suggested. First cell contains a script like this:

A2

"I dont like the brand"

B2

"Options : I don`t like the brand;I have not been approached by the sales team;The commission is low;The stock runs out quickly;Not allowed to have competition;The network coverage for the MSP is poor in the region;Repetitive technical incidents undermining my airtime sales;Availability of products at the level of sub-dealers / dealers;Bad experience with sales team;Bad experience with customer support;Stock shortage i.e. isn`t available;Demand is low"

Now the code is supposed to check if the exact same string in A2 can be found in B2...if not return false. The code passed all but 1 test i conducted. I took out the "I" from A2 but it still said the formula was true. Hows that possible?

Oscar,!! Brilliant formula, !!! However, Id like to get any and all matching value occurances in the dataset/table.

So it would return row1find,row2find,row3find etc - ideally illl like to get the row&column number of all the matches.

I've managed to do it for the 1st (and in this case, only) retrieval : by doing this in an adjecent column:

=MATCH(G47,INDIRECT("'Sheet1'!$D$"&X47&":$M$"&X47),0) where my grid of data to look at and search is in sheet1D7:M51 and X47 is the result of your formula on the data. Ideally would like to have your result be row1|row2|....|rown depending how many matches there are (the occurances of the value-string to find its occurances).

Yes, I could cocatenate the original data grid d7:m51,

so it reads in 1 column, d7|e7|f7|g7|jh7|i7|j7|k7|l7|m7

drag down to row 51, and do multi value return match against that,

similar to https://www.get-digital-help.com/2012/03/28/search-for-a-text-string-and-return-multiple-adjacent-values/#ab

but wondered if it could be done on the grid/table as it stands based on your original formula on this page.

That would be powerful.

Try this array formula:

=IFERROR(INDEX(Table1&(ROW(Table1)-MIN(ROW(Table1))+1)&","&COLUMN(Table1)-MIN(COLUMN(Table1))+1,SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")

I knew this was going to be super hard....

These the names im searching for:

B C # of times they appear in my table to Search in

Phillip Pettus 1

Lynn Greer 1

Marcel Black 1

Parker Moore 3

Danny Crawford 1

Phil Williams 3

Ken Johnson 2

Terri Collins 1

Ed Henry 1

Mike Ball 1

Randall Shedd 1

Corey Harbison1

Connie Rowe 1

Tim Wadsworth1

Allen Farley 1

Kyle South 1

for Wadworth, which appears in row 47 in the table, (and only once throughout) it returns: 4743||1

(ive changed the delimator to || ).

for Ball, I get 73||1

and for WIlliams (appers 3 times, first time in row 51), i get 5147||1

I cant make heads or tails of the output, further to fist 1 or 2 characters.

If the delimator is , or ||, the numbers its returning are just all 1 . Williams and Johnson should have 3 and 2 after it.

In anycase, you have helped me to think about this more logically, but the formula doesnt work.

The output ive got for the datA above is,

3935||1

2319||1

73||1

3531||1

1511||1

5147||1

3127||1

1511||1

2723||1

73||1

4339||1

2319||1

4339||1

4743||1

1915||1

4339||1

doent make sense given what I want it to do/identify. But we are getting somewhere...I believe.

I do thank you for your help regardless. Totally. You have no obligation to.

Total star. I just want to let you know that. Thank you.

David Wooley,

I believe the formula is working, 47 are the two first digits in 4743. 51 -> 5147 and so on. Are the two last digits perhaps column numbers?

What does the following formula return?

=IFERROR(INDEX(Table1&(ROW(Table1)-MIN(ROW(Table1))+1),SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")

P.s. I can live without getting the column nunbers, I can get those myself, If i get the apperaances per row, like row x, row y, row z , and or (perhaps as I think you were doing, highlighting the number of times the object-string appears in the table), I can work out the rest myself.

In anycase. Absolutely Fantastic world stopping, jaw-dropping Formula regardless !!! (just need the number of times the string appears! Not just the first time!) !!!!

No, Those other 2 numbers are not the columns.

South, located at row 43 column 10, returns "4339".

Johnson, located at row 31, column 2 and column 3, returns "3126"

Black, located in row 7, column 9, returns "72"

Garrett, located in row 23, column 9, returns "2318"

Rogers. row 34, Column 3, retuns "4338"

Martin, row 35 column 7, returns "3530"

I need to rest! So the first 2 (or 1) digits give me what your orignal formula did - the row number. But the last 2 (two). I have no idea. i need to rest and try to figure this out

I think you already have the answer Oscar, its :

=IFERROR(INDEX(Table1,SMALL(IF(FREQUENCY(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1)>0,ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(B1)),COLUMN(B1)),"")

but the results & its application, all in one cell..

Likewise, & I think this is better,

=IFERROR(INDEX(Table1,SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")

But need to adapt it to return only the row number of the occurances, and put them in the same output cell, concatonated.

Thank you Oscar! This was helpful.

Ash,

Thanks!

Hi Oscar,

Thank you for this post.

I have a somewhat related question, if you don't mind:

I have very large amount of text in a single cell, and I would like to extract multiple instances of text that appear between two specific words.

For example, here is the sample text in one cell:

{"date": 5/7/19 headline:"GE Posts Profit" source:"CNBC"}{"date": 5/8/19 headline:"GE Dividend Shrink" source:"MSN"}{"date": 5/9/19 headline:"GE Bankrupt" source:"WSJ"}

This following formula does a good enough job of extracting the first headline:

=MID(C2,SEARCH("headline",C2)+2,SEARCH("source:",C2)-SEARCH("headline",C2)-4)

However it only extracts the first headline and nothing after it.

If possible, I would like to extract all of headlines within the text in that cell, and generate a vertical array of those headlines so that it looks like this:

GE Posts Profit

GE Dividends Shrink

GE Bankrupt

Is this possible?

Thanks very much.

Hi Blake,

I built a user defined function, please see this article:

https://www.get-digital-help.com/2019/05/13/extract-text-between-words-udf/

Hi there

Hi there,

Wondering how can I extract all rows that where one of the cells that contains a specific text. Please refer to the url below.

I would like to extract the row that contains 'Lee' in the assistant column but with the 'tooth' status.

https://imgur.com/AnjB0Wi

Addelyn,

did you try the Advanced filter?

https://www.get-digital-help.com/search-for-a-text-string-in-an-excel-table/#adv

Use the asterisk * to filter rows that contain the search value. In your case: *Lee*

Hi Oscar,

I am prisilla tan and i have just started my excel learning journey (self learning) out of interest.

Firstly, thank you for this very interesting article.

I am intrigued to know.. what if there are multiple search strings if each table 1 cell has a unique value in it?

Search table

A123 A456 A789 A1011

B123 B456 B789 B1011

C123 C456 C789 C1011

D123 D456 D789 D1011

Search string 1: A456

search string 2: D789

search string 3: C1011

Search String 4:

Search Result 1: A123 A456 A789 A1011

Search Result 2: D123 D456 D789 D1011

Search Result 3: C123 C456 C789 C1011

Search Result 4:

Looking forward to understand/solve my queries from you :)

Please share how it would be done will be a great whenever you are available

Thank you

Hello Oscar,

Using the Advanced Filter option and your example data above, how would you find and select all rows containing either AA or DD (I have a sheet containing known misspellings of a person's name in any of three columns and I would like to select all the records together.)? You noted the use of OR-logic which I believe would work, but I do not know how to implement it in the criteria table.

Regards,

Jason

Hi Oscar,

Whenever I type a word into my search box the data doesn't refresh. It keeps the data from the first search I typed in. The one that I made the table with. Is there a way to get it to refresh after each search or is it supposed to be automatic and I'm missing something? Any help would be useful. Thank you!

-Kaitlin

Kaitlin

Yes, the data should refresh automatically unless you are using the Advanced Filter?

Is Automatic recalculation enabled?

1. Go to tab "Formulas" on the ribbon.

2. Press with left mouse button on the "Calculation Options" button.

3. Verify that Automatic is selected.

Hi Oscar,

I have an issue with this formula. When i put on array on a range of cells and let them calculate similarities with the cell C11 happens to duplicate the results from nowhere.

`=SI.ERROR(INDICE(SENTENCIADOS!$A$1:$F$30000;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR($C$11;SENTENCIADOS!$A$1:$F$30000));COINCIDIR(FILA(SENTENCIADOS!$A$1:$F$30000);FILA(SENTENCIADOS!$A$1:$F$30000));"");FILAS($A$1:A1));COLUMNAS($A$1:A1));"")`

Good Evening Oscar- thank you for putting this helpful formula together. Just wanted to send an note of appreciation from Boston, MA, USA.

Thank you, Emily.

Hi

Is possible to sum all WA11?

(A1) WA11 4

(A2) AdBlue 1, WA11 223

(A3) AdBlue 3, WA11 32, shift 4

... and everything is in one column.

Thanks you very much for your help.

Sincerely Marko