## Search for a text string in a data set using an array formula

**Question:**

**Answer:**

**Array formula in B13:**

Select cell range B13:E16.Â Click formula bar.Â Press + CTRL + SHIFT + ENTER.

Replace FIND function with SEARCH function if you donÂ´t want the formula case sensitive.

### Download Excel file

Enter your email to receive the workbook.### Explaining array formula in cell B13

**Step 1 - Identify cells containing search string**

Find function returns the starting point of one text string within another text string. ISNUMBER(FIND($C$9, $B$3:$E$6)) creates this array 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.

Recommended article

How to use the SEARCH function

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

**Step 2 - Return row number**

ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, "") returns this array:

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

Recommended post

The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]

**Step 3 - Sort the row numbers from smallest to largest**

Small function returns the k-th smallest number*.*

SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1) returns this array:

Related article

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 4 - Return a value at the intersection of a particular row and column**

**INDEX($B$3:$E$6, **SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1)**, COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1)**

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

*Step 5 - Remove errors*

=**IFERROR(**INDEX($B$3:$E$6, SMALL(IF(ISNUMBER(FIND($C$9, $B$3:$E$6)), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1, ""), ROW($B$3:$E$6)-MIN(ROW($B$3:$E$6))+1), COLUMN($B$3:$E$6)-MIN(COLUMN($B$3:$E$6))+1),** "")**

How to use the IFERROR function

If the value argument returns an error, the value_if_error argumentÂ is used.Â If theÂ valueÂ argument does NOT return an error, the IFERROR function […]

### Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Search for a cell value in a dataset:

Search for a cell value in a dataset

Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]

How to look for values in a column that contain two text strings:

Search for multiple text strings in column – AND logic

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

A record is returned if both search strings are found on the same row

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

Search each column for a string each and return multiple records – AND logic

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

Search for **multiple text strings** in **multiple columns**, one text string in each column. Return values in which **all text strings match**:

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Search for a single text string in a **single** column and return multiple matches.

Search for a text string 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 […]

Search for a text string in a **single** column and return multiple corresponding values.

Search for a text string 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 […]

Search for multiple text strings in a **single** column and return multiple corresponding values.

Search for a text string 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 […]

Search for a text string in **multiple** columns and return corresponding values.

Search for a text string and return multiple adjacent values

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Extract records containing digits [Formula]

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Extract records between two dates

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Extract negative values and adjacent cells

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Filter records based on a date range and a text string

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

### 38 Responses to “Search for a text string in a data set using an array formula”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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

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.