Index match on two sheets

Version 2.

Named Range List
=TRANSPOSE(GET.WORKBOOK(1))&T(NOW())

Named Range sheets changes to (This is good for up to 19 sheets of data. How many are there?):
=$M$2:INDEX($M$2:$M$20,SUMPRODUCT(--(LEN($M$2:$M$20)>0)))

In M1, copied down to M20 (array formula):
=IFERROR(INDEX(MID(List,FIND("]",List)+1,255),ROWS(M$2:M2),1),"")

I changed the name of the working sheet to Summary. Call it what you want, BUT... KEEP IT as the FIRST tab.

If/when you add new sheet(s), the formula in column M and all results will update AS SOON as you change any value, in any cell, on any sheet.