Domenic wrote...
I tried adopting Harlan's approach for your solution, but came across a
couple of issues...

1) Excel will not accept the formula unless defined names are used.

Perhaps this is version-dependent, but under Excel 2003 SP1 with the
following in Sheet1!A2:A9,
{"this";"is";"a";"test";"now";"is";"the";"time" }, and the following in
Sheet2!A2:A9,
{"the";"quick";"brown";"fox";"jumped";"over";"the" ;"lazy"}, I select
Sheet3!A1:A16 and enter the array formula

=T(INDIRECT("'"&INDEX({"Sheet1";"Sheet2"},1+INT((R OW(INDIRECT("A1:A"&
(8*COUNTA({"Sheet1";"Sheet2"}))))-1)/8))&"'!A"&(2+MOD(ROW(INDIRECT("A1:A"
&(8*COUNTA({"Sheet1";"Sheet2"}))))-1,8))))

and it returns
{"this";"is";"a";"test";"now";"is";"the";"time";"t he";"quick";"brown";"fox";
"jumped";"over";"the";"lazy"} as expected.

What was your exact formula that appeared to require defined names? Do
you mean fitting this into the OP's original formula requires defined
names in order to avoid the 7 nested function call limit? That's
likely.

2) It returns #NUM! in some instances.

Such as?

Nevertheless, try it and see if it works for you. Assuming that A2:B9
on each sheet contains your data, list the sheet names in a range of
cells and name this range WSLST. Then define the following...

Array1:
=1+INT((ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))-1)/8)

Array2:
=2+MOD(ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))-1,8)

Array3:
=ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))

If it were me, I'd define the last of these first then use it in the
definition of the other two. I'd probably also make each of the OP's
multiple worksheet blocks a defined name on it's own. So something like

WSLST: a single column, multiple row range containing worksheet names

N: the number of rows in the common ranges in each of the worksheets
in WSLST

S:
=ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1

Col_A:
=T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N))))

Col_B:
=T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N))))

then make the array formula

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)