Lookup a value in multiple sheets and return name of sheet

[celias]

Hello!
I have the following problem:
I have one workbook with 36 sheets.
All the sheets are alike: same structure, same column names. The data varies.
On each sheet I would like to look for each one of the Column 1 values (Member_Name) on each one of the previous sheets. If the name is there (and it can be on multiple sheets), then I would like to know the name of the first sheet where that name exists.

I started trying with IFNA combined with VLOOKUP, but having to nest up to 36 formulas doesn't work...
Among other options, I also tried using
=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A2900"),$A2),SheetList) after creating the sheets list, but this gives me the last sheet on which the member_name appears, and I wanted the first...

Any help?
Thank you in advance for your attention, your time and your help.

 

[celias]

Oh, wow! Thank you so much for the explanation. You really make it sound easy.
And yes, the file really got very, very heavy. When I replied to you before, I hadn't filled the entire workbook yet. I barely can work with it now.

Maybe this requires some high level programming that I don't know how to do it. If you have any other suggestions I would appreciate. For now I am thinking of continuing the work on separate workbooks that will look into the first one for the needed data. I don't know if this will do any help.

Thank you.

Hi, glad you got it working and a little surprised that you didn't have any problems with calculation time.