JG wrote...
....
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
INDIRECT("[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)
....

Note: the external reference above needs to start off as either

'[Filename.xls]MainQueryResult'!$B$2:

or

[Filename.xls]MainQueryResult!$B$2:

but not as you show it above. IOW, the single quotes need to enclose
the filename portion of the reference as well as the worksheet name,
or you need to dispense with them. Wrapping only the worksheet names
in single quotes is a reference syntax error, so Excel would CORRECTLY
return #REF!.

This isn't the best way to handle a problem like this. There's no good
reason to use the first INDIRECT call. If you enter this formula in
cell X99, you'd be better off replacing the first INDIRECT call with
$A99. If you need to protect against range insertion/deletion moving
cells in column A, replace the first INDIRECT call with INDEX($A:$A,SUM
(ROW())). [SUM(ROW()) is intentional and protects against very subtle
bugs that can arise because ROW always returns an array, even single
results as single value arrays, and that can cause problems. Wrapping
ROW() inside SUM converts the result to a scalar value.]

Since you're using a sorted lookup, you could check whether your
lookup value from col A would be found by the Count_Comp!A2 row of the
external table, and only if so perform a lookup. Otherwise, just
return the value at the Count_Comp!A2 row.

=INDEX('[Filename.xls]MainQueryResult'!$B$2:$D$65536,
IF(INDEX($A:$A,SUM(ROW()))INDEX('[Filename.xls]MainQueryResult'!$B
$2:$B$65536,Count_Comp!$A$2),
MATCH(INDEX($A:$A,SUM(ROW())),'[Filename.xls]MainQueryResult'!$B$2:$B
$65536),Count_Comp!$A$2))

This should recalc more quickly. It also avoids volatile functions.