Perhaps try a non-array formulas play
which would also deliver the desired results ..

Source data in cols A and B from row2 down,
col A (BigList) to be compared against col B (LittleList),
with matched items in BigList sliced into col C,
unmatched items in BigList sliced into col D

The extract cols:

In C2:
=IF(ROW(A1)COUNT(E:E),"",INDEX($A:$A,MATCH(SMALL( E:E,ROW(A1)),E:E,0)))
In D2:
=IF(ROW(B1)COUNT(F:F),"",INDEX($A:$A,MATCH(SMALL( F:F,ROW(B1)),F:F,0)))

The corresponding criteria cols:

In E2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),ROW(),"" ))
In F2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW() ))
(Leave E1:F1 empty)

Then just select C2:F2 and fill down to the last row of data in col A
(BigList)
(You can fill beyond the current extent of BigList to its max expected
extent in col A. But for calc efficiency, just fill to the smallest extent
which suffices to cover)

Cols C and D will return the required results
(Hide away the criteria cols E and F, if necess)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BrandonC" wrote:

I have just a very simple thing to accomplish but I guess I am just not
getting it. I am comparing two columns of data, larger against small
column, taking the matching results to a separate column then the
non-matching column to another. I have used a dynamic range for each
column.
Biglist Littlelist Matches NonMatches
123456789 123654789 123654789 236548925
123456789 245987445 665713235 461876212
236548925 124598792 123456789 114532215
461876212 665713235 165645136
124598792 123436789 245987445
114532215 124598792
665713235
165645136

I have used the differant examples here, and elsewhere and I am not
able to get the Functions to work with INDEX or MATCH even when nesting
the Formula.

Any help would be very appreciated.


--
BrandonC
------------------------------------------------------------------------
BrandonC's Profile: http://www.excelforum.com/member.php...o&userid=36504
View this thread: http://www.excelforum.com/showthread...hreadid=562665