=--(COUNTIF(A1:B3,E1)>0)
where E1 houses a value like "joe b".
which will give 1 if success, otherwise 0.
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
=--(COUNTIF(A1:B3,E1)>0)
where E1 houses a value like "joe b".
which will give 1 if success, otherwise 0.
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 --Originally Posted by gaftalik
=IF(COUNTIF('Other Sheet'!A1:A50,A1),"EXISTS","NOT")
where A1 in this sheet houses your target value such as PL small
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
hey aladin,
is there any solution if i would like to search a approx text from a text string... where should be spel mismatchs
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
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
Like this thread? Share it with others