You could condense the formula courtesy of an INDIRECT call, however, this would in turn make the calculations Volatile.
If you opt to use INDIRECT then I would suggest you take other steps to utilise the fact that your data is stored in Ascending order (in short, using Binary Search based lookups).
This will improve efficiency which though as you say is already good - once Volatile you will want to improve further.
(Volatiles will calculate more often so it's a good idea to make them as fast as possible).
I would first suggest that in a single column on your Summary sheet you establish the row position of the matching item (on the appropriate sheet), this reduces no. of repetitive calcs in remaining columns, eg:
Z2:
=IFERROR(MATCH($E2;INDIRECT("'[Lookup.xlsx]"&LEFT($E2)&"'!$A:$A"))*(LOOKUP($E2;INDIRECT("'[Lookup.xlsx]"&LEFT($E2)&"'!A:A"))=$E2);0)
copied down
where the value in E2 is not found in the appropriate sheet in Lookup.xlsx the result will be 0.
In your other calcs you can then use the value in Zn to retrieve the data via an INDEX/MATCH call (the MATCH having been conducted in Col Z), ie this:
IF(LEFT(E2,1)="A",VLOOKUP(E2,[Lookup.xlsx]A!$A$1:$E$24521,2,FALSE),
IF(LEFT(E2,1)="B",VLOOKUP(E2,Lookup.xlsx]B!$A$1:$E$169530,2,FALSE),
IF(LEFT(E2,1)="C",VLOOKUP(E2,Lookup.xlsx]C!$A$1:$E$148162,2,FALSE),
etc. down to
IF(LEFT(E2,1)="Z",VLOOKUP(E2,[Lookup.xlsx]Z!$A$1:$E$631,2,FALSE)
becomes
=IF($Z2=0;"";INDEX(INDIRECT("'[Lookup.xlsx]"&LEFT($E2)&"'!B:B");$Z2))
copied down
Let us know if the above approach helps at all.
Bookmarks