Harlan, this works beautifully! Thanks very much!

Cheers!

In article .com,
"Harlan Grove" wrote:

Fin Fang Foom wrote...
I maded the modifications to this:

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))))
...
Now it gining me a #NUM! error.

Are the above name ranges are correct?

Above definitions are correct possibly except for Col_B. Do your column
B ranges contain text or numbers? If numbers, you need to change the
T(..) call to an N(..) call.

However, there's a problem. The Col_A and Col_B defined name formulas
work when entered directly into multiple cell ranges, but not as terms
in longer formulas. Yet another defined name is needed.

XWSLST:
=T(OFFSET(WSLST,INT(S/N),0,1,1))

then change the defintions of Col_A and Col_B to

Col_A:
=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Col_B:
=T(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) if text
=N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) if numeric

The array formula

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

would still return #NUM! when there are no instances of the C2 value in
Col_B. If you want to trap such errors, try something like

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