This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(6.4, 40%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
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.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(92.8, 0%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDT%3C/text%3E%3C/svg%3E)
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
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.
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Hi @television
May I know if you have got any chance to check my answer? If you need further assistance, please feel free to let me know. Thank you!
AI answer
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: