XLookup Across multiple sheets

Hi All,

First-time post, I'm looking for some help with the XLookup function. I'm creating a spreadsheet for tracking student grades, and am trying to create a 'search' sheet, which will look up the details of a student across all the sheets at once.

So far, I've managed to get the standard XLookup function working (=XLOOKUP($I$15,Demo!$B$3:$B$32,Demo!A3:A32)) but only for an individual sheet. I have looked at nested XLookup functions, but with 18 sheets (one for each class) that would get very unwieldy very quickly, and hard to edit if I needed to rename the sheets. Is there an easier way to do it, potentially involving indexing the sheet names in a separate sheet? I've manged to get that working so it tells you which sheet the student is on, but am struggling to get that to work within the XLookup function. Ill attach the spreadsheet I'm working on (with fake data inside) which might make it more obvious, I realise my explanation may be unclear.

Thank you for any help,

Kind regards,
Nathan

You could place this formula in B7. Clear B7:T7 first

Please Login or Register  to view this content.

How about in A8 only

Formula:

Please Login or Register  to view this content.

Another option, rather than having a list of sheet names on another sheet, you could create a defined name (I called it ShtNames) using

Formula:

Please Login or Register  to view this content.

and then you can use

Formula:

Please Login or Register  to view this content.