Hi Harlan,

It looks like it's working I need to test it a couple of times just to
make sure but it looks good. The solution you provided is very handy
when doing a lookup across worksheets that accounts for duplicates.
This what I have:


Col_A =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))
Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N))))
N =8
S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST =Sheet1!$A$2:$A$3
XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1))


and using this formula


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


an-array


Ctrl,Shift,Enter


Harlan when you get a chance can you explain how all these formula
work?




Domenic wrote:
Sorry Harlan, I just noticed your recent post where I think you've
addressed the problem. I'll be taking a closer look at it...

Thanks!

In article ,
Domenic wrote:

Harlan,

If I follow the instructions for Part 1 of your post, I have no problem.
It works beautifully. I can select Sheet3!A1:A16, enter the array
formula, and it returns the correct values.

It's when I try to fit it into the OP's original formula that defined
names need to be used and that the formula doesn't seem to work. Here's
the situation as I understand it...

Sheet1!A2:A9 contains:

{"A";"B";"C";"D";"E";"F";"G";"H"}

Sheet1!B2:B9 contains:

{10;12;11;18;12;20;26;28}

Sheet2!A2:A9 contains:

{"I";"J";"K";"L";"M";"N";"O";"P"}

Sheet2!B2:B9 contains:

{20;10;18;20;12;14;14;16}

Sheet3!C2:C17 contains:

{10;10;11;12;12;12;14;14;16;18;18;20;20;20;26;28}

Defined names:

N:

8

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:

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

Lastly, the following formula is entered in Sheet3!D2, and copied down:

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

...which returns...

A
A
C
B
E
B
#NUM!
#NUM!
#NUM!
D
D
F
F
#NUM!
G
H

As you can see, the formula doesn't seem to work. It doesn't return the
corresponding values in Column A of Sheet1 and Sheet2 for Column C in
Sheet3. At least not on my version of Excel (Macintosh Excel v.X).
Now, for the $64,000 question. :) Does this work on your system?

By the way, I like how you've defined the formulas. It allows the final
formula to look a lot nicer...