• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to lookup a value across multiple sheets and return *ADDITION: all* sheet names

Not open for further replies.


New Member
Hi all,

I attempted to ask this in a thread, but it was SOLVED so I am not sure it could be seen.

In this posting--> http://chandoo.org/forum/threads/ho...-return-sheet-names-solved.11464/#post-176293

I found this formula
and it worked with great success, however:

@NARAYANK991 or @Hui , ninja masters from the above thread,

The formula only returns the first instance (sheet) wherein the value appears and I need all of the sheet names wherein the value appears.

Can we re-write the formula to return the name of the 2nd sheet the value/name appears in, the 3rd sheet, the nth sheet name... into separate column cells? Then I can combine those as needed.
Hi @Hui,
No, I hadn't, sometimes it's difficult to search for topics.

Without trying out this formula processes in depth, I'm still not sure what this method returns. Will I be able to get a formula to enter into separate cells to return each sheet name the value is present in? My value /name may be present in one, some, or all of 30 sheets and I want each sheet name in a new column across the row in which that value/name lives.
Hi @Hui,

Thank you for your assistance. I am a semi novice yet enthusiastic excel learner, do you have a concrete example of how I might slightly modify the final formula you linked? If not, I will play around with it when I get to my desktop.
Ohhh thank you! In my searches, I found the formulas for SMALL and with my limited knowledge, needed more guidance to pursue it. The result/output you show on your . Xlsx is exactly what I'm looking for (as I already set up the rest of my sheet to refer to individual cells with the sheet names). Sorry to answer too soon, I'm still away from my desktop, but this looks promising, so thank you! I will post an update later.
Hi again @NARAYANK991 .
Thank you again for your advice. Your formulas in the book you shared work, yet they do not return all sheets, it is skipping the "second" instance (sheet) the value is appearing in. I made an example workbook if you can possibly review it for an error.


    270.4 KB · Views: 36
Hi again @NARAYANK991 .
Thank you again for your advice. Your formulas in the book you shared work, yet they do not return all sheets, it is skipping the "second" instance (sheet) the value is appearing in. I made an example workbook if you can possibly review it for an error.

In this updated sample for you, I realized, sort of, how this is working. It appears that the formula is using the columns to know which sheet to refer to (or something along those lines), and I had a different formula for the first instance, so since column B was being used for something else, I couldn't see the second instance.
--How can I use column A for my names and still have my sheet names start in column B?


    276.4 KB · Views: 23
Hi ,

Can you see this file , and let me know whether it requires any changes ?



    269.1 KB · Views: 115
Hi ,

Can you see this file , and let me know whether it requires any changes ?

Thank you, @NARAYANK991 ! That did the trick, the secret was adjusting the cell reference to the right of COLUMN to (A1) to begin listing the sheet names. For anyone interested, here is the formula (placed in any column) to return the first sheet name that the value ($A1) is in, in the workbook, based on a Sheet List named range (Sheet_List) that contains all sheet names.

 {=IFERROR(INDEX(Sheet_List,SMALL(IF((1=--(COUNTIF(INDIRECT("'"&Sheet_List&"'!$B$1:$B$400"),$A1)>0)),ROW(Sheet_List) - MIN(ROW(Sheet_List)) + 1),COLUMN(A1))),"")}
=IFERROR(INDEX(Sheet_List,SMALL(IF((1=--(COUNTIF(INDIRECT("'"&Sheet_List&"'!$B$1:$B$400"),$A1)>0)),ROW(Sheet_List) - MIN(ROW(Sheet_List)) + 1),COLUMN(A1))),"")
+Ctrl+Shift+Enter (From post above).

From here, you can pull the formula over into adjacent columns (as far as you need to represent all sheets in the workbook).
This question has been SOLVED, can I mark the original post as such?

@LAP There are members still posting to this thread
Last edited by a moderator:
Re: test for sheet name spreadsheet: - Is it possible to write the search code so that it will still return the worksheet code if only a portion of the search string is found (i.e. if I search for 'VAR2' it will still find 'TESTVAR2' ?)
I need this as I have a spreadsheet with multiple recipes and I want to search to find every tsheet where 1, 2 or 3 particular ingredients are mentioned together in one worksheet - for example I want to find recipes where sugar, flour and milk is mentioned together and find the work sheets (e.g. the recipes which have 1, 2 or all three together) - but I want to search for 'Sugar' rather than 'white sugar', 'brown sugar', 'caster sugar' AND 'flour' rather than 'white flour', 'brown flour', 'wheat flour', 'corn flour' etc).
Is it possible to rename the sheets as text rather than as numbers/dates - will it still return a text name for the searched and found work sheet?

Thanks in advance!
thanks for your very fast response! I've attached a minorly modified version of the 'test for sheet name' spreadsheet which you uploaded yesterday - but I've put in a few 'test' recipes.
For example searching for 'sugar' will find 'sugar' but not 'white sugar'. I was wondering if a search formula could find the partial string 'sugar' within 'white sugar' and return the worksheet name. I'd like to rename the work sheet 'muffins' or whatever the recipe is rather than a number - 1984 for example. I'm not sure if the formula as currently written will return a text value for the worksheet rather than a number (or year-date)?
Many thanks!



    245.2 KB · Views: 28

I am trying to convert the example you provide in the "TEST FOR recipe SHEET Names RETURNS" file however, I am having trouble re-creating it on my own sheet.

I am running a quick formula to generate a listing of all of the sheets in the workbook, which I end up using as the "Sheet_List" defined name that you have; however in my workbook, I am naming it AE_One see below for adjusted formula.

=IFERROR(INDEX(AE_one,SMALL(IF((1=--(COUNTIF(INDIRECT("'"&AE_one&"'!$AE$1:$AE$400"),"*" & $B29 & "*")>0)),ROW(AE_one) - MIN(ROW(AE_one)) + 1),COLUMN(D29))),"")

AE_one - Defined as the listing of all of the sheets.
$AE$1:$AE$400 - Defined as the range location in each sheet that I am looking for.
B29 - Is the defined as the actual word I am searching for
D29 - Defined as the column prior, which i am assuming is used to make it provides the next sheet instead of the prior sheet.

The end result I am getting is nothing but blanks. Not sure what's missing her See attached image for details.

P.S. - I cannot upload the actual workfile as it has some confidential information which I cannot share with the internet.

Think you could help me? See below for the screen


  • upload_2017-10-3_18-49-14.png
    147.6 KB · Views: 16
Hi All,

As this is my first post on this forum, please feel free to point out if I am making any newbie mistakes in etiquette, etc!

The above thread has proved (almost) the perfect answer to an issue I was having with a need to take a list of parts and check through a collection of product sheets and report all those that each part appears on.

I created the list sheet with all product sheets names (TabNames) and the links to shortcut to them. The product sheets all have the same layout.

I then created another sheet with all part descriptions in Column A and codes (alphanumeric) in column B.

For Columns C through CX (100 columns) I then put in the following formula, slightly amended to take account of the columns:

{=IFERROR(INDEX(TabNames,SMALL(IF((1=--(COUNTIF(INDIRECT("'"&TabNames&"'!$B$1:$B$100"),$B2)>0)),ROW(TabNames) - MIN(ROW(TabNames)) + 1),COLUMN(B2))),"")}

At first I thought this worked properly as the first few parts I checked had all product sheets they appeared on, listed. Then it became clear that some parts had none of the sheets listed and some had just some. It's very inconsistent.

I checked already for erroneous leading space characters and even for CHAR(0160), removing them where found, which did improve the accuracy.

I'm stumped but still do not have a correct, working lookup of occurrences.

Can someone offer any advice? Thanks!
Hello all,

I am new here and I have an issue similar to the one in this thread.
I need to get all the sheet names for the items in the list starting with column B.
I tried to mimic the formulas above but I failed. Could someone explain why?
Could be a simple answer, but I just can't figure it.
Thank you.


  • TH1 mat map.xlsx
    543.1 KB · Views: 0


As You're new here - You've just read from Forum Rules
  • Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
This few years old thread is closed now.
Not open for further replies.