# Find a text within a range of cells

This is a discussion on Find a text within a range of cells within the Excel Questions forums, part of the Question Forums category; Hi, A B joe b elena c tony bob c joe a tom i need a formula if joe b ...

1. ## Find a text within a range of cells

Hi,

A B

joe b elena c
tony bob c
joe a tom

i need a formula if joe b exists in A1:B3 return "yes" other wise "NO"

Thank you

Gaftalik

2. ## Re: Find a text within a range of cells

=--(COUNTIF(A1:B3,E1)>0)

where E1 houses a value like "joe b".

which will give 1 if success, otherwise 0.

3. ## Re: Find a text within a range of cells

Thank you for your prompt answer, i am afraid i couldnt solve that problem , i will try to be clear :

in a sheet i have a set of products say: PL small, PL med, PL large, i want to check if they exist in the other sheet table A1:A50 and then return "EXIST" or "NOT"

Thank you ,
Gaftalik

4. ## Re: Find a text within a range of cells

Originally Posted by gaftalik

Thank you for your prompt answer, i am afraid i couldnt solve that problem , i will try to be clear :

in a sheet i have a set of products say: PL small, PL med, PL large, i want to check if they exist in the other sheet table A1:A50 and then return "EXIST" or "NOT"

Thank you ,
Gaftalik
Aladin's formula gets you what you need. What you want would be --

=IF(COUNTIF('Other Sheet'!A1:A50,A1),"EXISTS","NOT")

where A1 in this sheet houses your target value such as PL small

5. ## Re: Find a text within a range of cells

Yeah you was right , the first formula worked as well as the second one , sorry but it was my mistake, thanks to both of you !

gaftalik

6. ## Re: Find a text within a range of cells

is there any solution if i would like to search a approx text from a text string... where should be spel mismatchs

7. ## Re: Find a text within a range of cells

What about if you want to find a text string that is returned by a formula?

i have a few cells with formulae of this ilk:

if(a1<>a2*1.2,"boo boo here: vat error","")
if(and(b1>0,b2>0,"boo boo here: can't both be true","")

and i want to search the document for "boo boo" and return each one...

so countif works fine, but that just counts them
I wouldn't mind using match (column by column as it can't do multiple ones at once), but it counts cells with "*boo boo*" in the formulae, rather than just those that output the text string in question...
Can you put conditions inside the match function that discludes formulae
for example (though this doesn't work):

=match(and("boo boo*",<>",?boo boo"),a1:a999,0)

then as a secondary issue, once i've got that working, how do you use match or another function to return the second, third or fourth instance of a reoccurring text string..?

I'll be quite impressed with the person who solves this.
Cheers,
thegurumonkey

8. ## Re: Find a text within a range of cells

Originally Posted by thegurumonkey
What about if you want to find a text string that is returned by a formula?

i have a few cells with formulae of this ilk:

if(a1<>a2*1.2,"boo boo here: vat error","")
if(and(b1>0,b2>0,"boo boo here: can't both be true","")

and i want to search the document for "boo boo" and return each one...

so countif works fine, but that just counts them
I wouldn't mind using match (column by column as it can't do multiple ones at once), but it counts cells with "*boo boo*" in the formulae, rather than just those that output the text string in question...
Can you put conditions inside the match function that discludes formulae
for example (though this doesn't work):

=match(and("boo boo*",<>",?boo boo"),a1:a999,0)

then as a secondary issue, once i've got that working, how do you use match or another function to return the second, third or fourth instance of a reoccurring text string..?

I'll be quite impressed with the person who solves this.
Cheers,
thegurumonkey

what you are exactly looking for ?
if you want the text to be printed then its not possible because if u select the range then it will return more than one value at that time how it can be placed in one cell think of it...

cheers.
Darshan

9. ## Re: Find a text within a range of cells

I have a little bit complicated question....

I have a large XLSX with 80 sheets of mixed data imported from different collaborators. A real mess.

I want find a text inside a text string in a table (or in a full sheet).

example

A B

joe b elena c
tony bob c
joe a tom

I want know how many joe there are, or if at least 1 joe exist in the table (I don't need to find the number of occurence or the coordinates...just nwws to know if at least one exist in that sheet) .

Possible ?

10. ## Re: Find a text within a range of cells

Originally Posted by Paolo66
I have a little bit complicated question....

I have a large XLSX with 80 sheets of mixed data imported from different collaborators. A real mess.

I want find a text inside a text string in a table (or in a full sheet).

example

A B

joe b elena c
tony bob c
joe a tom

I want know how many joe there are, or if at least 1 joe exist in the table (I don't need to find the number of occurence or the coordinates...just nwws to know if at least one exist in that sheet) .

Possible ?
hi Paolo,

COUNTIF was mentioned earlier in this thread, it can also be used to search string within another string using * wildcard in the criteria parameter.

you can search for appearances of Joe in a range of cells using
=COUNTIF(B1:D4;"*Joe*") --> you'd still find it even if it's in the middle of the string in a cell

you can also dynamically defince the text you're looking for, in case you have Joe in F13, the search formula would be
=COUNTIF(B1:D4;"*"&F13&"*")

to check an entire sheet, for example a sheet named 'notes' just specify a large enough range that sure contains all data, like entire columns from A to ZZ:
=COUNTIF(notes!A:ZZ;"*"&F13&"*")

as far as i know excel doesn't allow adding an entire sheet as range as a function parameter

For a boolean TRUE/FALSE result about Joe's existence just add an IF function around it, that would verify whether COUNTIF if result is 0 or anything else

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•