SEARCH function

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Are we looking for Voltaire in a single cell of each sheet or in a specific range of each sheet?
 

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30
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.
 

TOKYOJ1

New Member
Joined
Aug 14, 2017
Messages
30

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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.


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Voltaire</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">where</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">freq</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">111</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">200</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">303</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p>

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

New Member
Joined
Aug 14, 2017
Messages
30

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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&"*"))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,620
Messages
5,523,944
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top