Find a text within a range of cells

[gaftalik]

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

 

[Paolo66]

Perfect!
Thanks a lot!

 

[Paolo66]

Next Step_
And If I like to search in all sheets in one shot ?

Example:

here the special counting is limited to -notes- sheet =COUNTIF(notes!A:ZZ;"*"&F13&"*")

there is some way to use the wildcards for sheetname like an immaginary =COUNTIF("*"!A:ZZ;"*"&F13&"*")

 

[istiasztalos]

Hi Paolo,
To my knowledge there's no simple way to just tell Excel to check all existing sheets.
You'll need at least a list of the names of sheets. Then you can use array formula to check all from that list.

Let's say you listed your sheet names that you'd like to search in a column ranging from A1 to A20 (and D1 contains what you'd like to find), then you can use this formula, to apply the search to all sheets. INDIRECT lets you create the sheet references using a formula and the Array feature applies it to all of them that you listed. It is very important that after adding the formula in the bar do not press Enter! Press instead Ctrl+Shift+Enter, this is how you apply Array formulae.
=SUM(COUNTIF(INDIRECT("'"&A1:A20&"'!A:ZZ");"*"&D1&"*"))
when done correctly, it will appear in brackets like this:
{=SUM(COUNTIF(INDIRECT("'"&A1:A20&"'!A:ZZ");"*"&D1&"*"))}