Find a text within a range of cells

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
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 (y)

Gaftalik
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Dear Aladin,

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
 
Upvote 0
gaftalik said:
Dear Aladin,

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
 
Upvote 0
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
 
Upvote 0
hey aladin,
is there any solution if i would like to search a approx text from a text string... where should be spel mismatchs
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top