Lookup across multiple worksheets and get in which sheet a specific text is located

[mromano]

Good morning,
I am organizing the program of an event and created a workbook containing multiple sheets (one for every day with an event).
In each sheet, I insert the names of the speakers. Afterwards, I want to generate a summary in another sheet where it is shown the name of each speaker and the date when he/she discuss the thesis.
Is it possible to use a formula to lookup across multiple worksheets and get in which sheet a specific name is located, to generate such a summary?
Ideally, I would like to get in the summary the name of the sheet. The presence of a duplicate should just generate an error in the formula.
However, to simplify, I inserted the date of the discussion next to the speaker name.
Thank you very much!
Maurizio

[hemesh]

what do you mean by duplicates , is it one name in two sheets or one sheet having two entries of same name

[kvsrinivasamurthy]

ARRAY formula in B2 thn copied down.

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

[mromano]

Thank you for kind replies
Dear hemesh, regarding duplicates , it is one name in two sheets.

Dear kvsrinivasamurthy, I tried to enter the array formula but I am now facing with another problem. I am using Excel for mac 16.34 (office 2019).
I entered that formula then tried either of the keyboard shortcuts below:

press CONTROL + U and then press COMMAND + RETURN

press CONTROL + U and then press COMMAND + SHIFT + RETURN

but I don't see the curly brackets

so the formula is not working....

[kvsrinivasamurthy]

I do not know about Mac.
Just try Ctrl+Shift+Enter.
Try Googling for entering Array formula in mac.

[mromano]

I shifted to windows-Excel and followed your instructions.
Apparently, I entered the array formula but it is not working.
I am attaching a screenshot of the excel summary sheet. Am I doing something wrong?
Thank you
Attachment 666374

[kvsrinivasamurthy]

You have not entered the Sheet names in K2:K6 of summary sheet. Plsee the file once again.
enter all sheet names in text format.

Eg: '24-03-2020

[mromano]

Great! It is working
Thank you very much!!