Excel sheets

television 0 Reputation points
2026-09-16T06:59:41.8766667+00:00

So I have many sheets on my Excel file. It goes like: S1 has a list of name, and S2, S3, ... are a sign up list of people. For example, Siz is a name on S1, and sign up on S4 & S5.

My question is: what formula(s) can I use to do things like these in S1: Find(Siz) on all the sheets, if (Siz) appear on S4 then return "S4" to the cell. I expect the result goes like "Sheet4", "Sheet5" in the cell where I have the formula.

Please help me guys. Thank you so much.

Microsoft 365 and Office | Excel | For business | Windows
0 comments

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-09-16T06:59:55.9933333+00:00

    Use a formula that checks each signup sheet and joins the matching sheet names into one cell.

    Example for S1, if the name is in A2 and the signup sheets are S4, S5, and S6:

    =TEXTJOIN(", ",TRUE,
 IF(COUNTIF(S4!A:A,A2)>0,"S4",""),
 IF(COUNTIF(S5!A:A,A2)>0,"S5",""),
 IF(COUNTIF(S6!A:A,A2)>0,"S6","")
)

    How it works:

    • COUNTIF(S4!A:A,A2)>0 checks whether the name in A2 appears on sheet S4.
    • IF(...,"S4","") returns the sheet name if found, otherwise blank.
    • TEXTJOIN(", ",TRUE,...) combines all matching sheet names, such as S4, S5.

    If Siz is in A2 on S1 and appears on S4 and S5, the result will be:

    S4, S5

    If the name might be on many sheets, extend the same pattern:

    =TEXTJOIN(", ",TRUE,
 IF(COUNTIF(S2!A:A,A2)>0,"S2",""),
 IF(COUNTIF(S3!A:A,A2)>0,"S3",""),
 IF(COUNTIF(S4!A:A,A2)>0,"S4",""),
 IF(COUNTIF(S5!A:A,A2)>0,"S5","")
)

    If the names are not in column A on the signup sheets, replace A:A with the correct column or range.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Dora-T 1,050 Reputation points Independent Advisor
    2026-09-16T07:21:56.1366667+00:00

    Hi @television
    Based on your description, I tested a similar scenario in my environment by setting up multiple worksheets. I used S1 as the main sheet containing the list of names, while S2, S3, S4, and S5 contain the signup lists.

    For example, if "Word" appears in S3, S4, and S5, I used the following formula in S1 to return the names of the sheets where the name was found:

    =TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&{"S2","S3","S4","S5"}&"'!A:A"),A2)>0,{"S2","S3","S4","S5"},""))

    The formula returns the matching sheet names in the cell, for example: S3, S4, S5

    User's image

    Additionally, you can refer to the following AI-generated answer for another approach to your scenario.

    I hope this helps. If I have misunderstood your scenario or the way your worksheets are structured, please feel free to correct me and provide more details about your setup.

    If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment".  

    Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

    Was this answer helpful?

  3. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2026-09-16T23:17:28.06+00:00

    Hi,

    It will be ideal to append data from multiple tabs into a single one. Once that is done, you may simply just use Data > Filter or the FILTER() function.

    Please watch this video to learn the process of appending

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.