Does it need to be dynamic - or do you just need to assemble/create the
list. For the latter

You could put a dummy column next to the second list

use
=if(countif(first list,first cell in second list),"Match","No Match")

then drag this down the column

Do an autofilter on these two columns (Data=Filter=Autofilter)

On the dummy column in the dropdown select No Match

Select the second list items an copy the cells (only the visible cells will
be copied)

Paste at the bottom of the report list.

Use the Vlookup part of your formula to populate the top of the list.

--
Regards,
Tom Ogilvy

"SHAWN" wrote in message
om...
Hi,

I'm working on a report, where I have a column of values ("List 1")
and I need to do a lookup against some other lookup table ("W2Ord").
Then, when all the lookups are done, the formula continues and looks
for values that are not found in the "List 1" field, but found in the
"W2Ord".

I had troubles figuring out how to accomplish this without VBA and
Harlan Grove has kindly offered a solution to my problem, which is an
array formula, that looks like this:

{=IF(NOT(ISERROR(VLOOKUP(A6,W2Ord,1,FALSE))),

VLOOKUP(A6,W2Ord,1,FALSE),IF(A6="",INDEX(W2Ord,MAT CH(0,COUNTIF(C$5:C5,W2Ord)
,0)),""))}

Basically, it says if a value is found in the W2Ord table, then do a
lookup, otherwise, if there is no values in column A, look at all
values above and bring me a value from W2Ord, that doesn't have a
match in List 1.

List 1 List 2(this is a calculated field - the array formula)
123 123
126 ---blank since no value exists in W2Ord
128 128
134 ---- this value was found in W2Ord but no in List
1
135 .....etc etc

The formula works fine and does what I need. The problem is that there
are about 300 to 500 values in List 1 and about 1500 to 2000 in the
lookup table (W2Ord).
When I copy the formula down, the lookups work nice and fast. It's
when it starts looking for "no matches" it gets really slow and after
600-700 th record literally takes forever to calculate.

I tried to solve the problem programatically - looping copying 50
records at a time down and then pasting them special as values to
avoid recalculation of a large number of formulas - to no avail - I
would stop the macro after 25 minutes and it would still be in the
900th record range. I tried copy/paste formulas down, filling formulas
down, - nothing would work. I've read somewhere that array formulas
might not be a very good solution for this kinda job but can't think
of anything as an alternative.

Can anybody help me here.

TIA.