SEARCH function

[TOKYOJ1]

Hi,

I would like to search of repeating words in excel across multiple worksheets and display the results as sheet numbers and frequency table::

Eg:
Voltaire

Sheet 111
Freq: 3

Sheet 200
Freq: 6

Sheet 303
Freq: 1

Can somebody advise the formula for that?

Thanks
TJ

 

[Aladin Akyurek]

Are we looking for Voltaire in a single cell of each sheet or in a specific range of each sheet?

 

[TOKYOJ1]

I am looking for Voltaire across the whole workbook.

I can do this using the "Find and Select" tab but I need to use a formula for this purpose.

 

[Aladin Akyurek]

I am looking for Voltaire across the whole workbook.

I can do this using the "Find and Select" tab but I need to use a formula for this purpose.

That's not what I asked...

 

[TOKYOJ1]

Apologies, the correct response should be specific range within each sheet: A1 to A20

 

[Aladin Akyurek]

Apologies, the correct response should be specific range within each sheet: A1 to A20

Create a range which houses the names of all of the relevant sheets, i.e. 111, 200, 205, 300, 303, etc.
Select this range and name the selection SheetList.

Book1
	A	B
1	Voltaire
2	where	freq
3	111	3
4	200	1
5	303	2
Sheet1

A1 houses a search word of interest.

In A3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(SheetList,SMALL(IF(COUNTIFS(INDIRECT("'"&SheetList&"'!A1:A20"),$A$1)>0,ROW(SheetList)-ROW(INDEX(SheetList,1,1))+1),ROWS($A$3:A3))),"")

In B3 just enter and copy down:

=IF($A3="","",COUNTIFS(INDIRECT("'"&$A3&"'!A1:A20"),$A$1))

 

[TOKYOJ1]

Hi,

The formula works great.

Is it possible to search for a specific word within a string of text within a cell?
Eg: Sheet 10: Cell A1:"As Voltaire was walking along..."

Regards,

 

[Aladin Akyurek]

Hi,

The formula works great.

Is it possible to search for a specific word within a string of text within a cell?
Eg: Sheet 10: Cell A1:"As Voltaire was walking along..."

Regards,

Add wildcard around the A1 calls... That is:

=IFERROR(INDEX(SheetList,SMALL(IF(COUNTIFS(INDIRECT("'"&SheetList&"'!A1:A20"),"*"&$A$1&"*")>0,ROW(SheetList)-ROW(INDEX(SheetList,1,1))+1),ROWS($A$3:A3))),"")

and

=IF($A3="","",COUNTIFS(INDIRECT("'"&$A3&"'!A1:A20"),"*"&$A$1&"*"))

 

[TOKYOJ1]

Works wonderfully!

 

[Aladin Akyurek]

Works wonderfully!

Great. Thanks for the update.