Hi Hari,

Sort your data and try this: it will calculate hundreds or thousands of
times faster.
Sorted Data with Missing Values.
Two approximate Lookups are usually faster than one exact Lookup.
If you can sort your data but still cannot use approximate match
because you can't be sure that the value you are looking up exists in the
lookup range, then try this:

IF(lookup_val=Index(lookup_array,MATCH(lookup_val, lookup_list),1)
,Index(lookup_array,MATCH(lookup_val,lookup_array) , colnum),"notexist")

This does an approximate lookup on the lookup list, and if the lookup
value = the answer in the lookup column you have found an exact match, so
redo the approximate lookup on the column you want, otherwise it's a missing
value. Note that this assumes you never lookup a value smaller than the
smallest value in the list, so you may need to add a dummy very small entry
into the list.
Two approximate matches are significantly faster than one exact match
for a lookup over a large number of rows (breakeven point is about 10-20
rows).


--
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com


"Hari" wrote in message
ups.com...
Hi,

My colleague has a file of size 31 MB (56000 rows) in which 2 columns
are of interest (Column B and F)

He also has another smaller file of 7MB which has 10000 rows. In this
file, he has to check whether data in Column Z appears in Column B and
F of bigger file or not. (Its like BigFile!B2&BigFile!F2 can be equal
to SmallFile!Z).

If it exists then we use a IF formula along with ISERROR to say that
"Data exists" or "Data Not Exist". Once we get the results we just do
copy -- paste special -- values so that each time editing of some other
column of the small file excel doesnt hang excel because of
recalculation of lookups.

The formula is working fine and am getting correct results.

But because of the size of the big file, it takes a lot of time to
calculate. I wanted to know as to whether we may load the big data file
in to access and then from excel I perform something like a lookup to
the access database. Is such a thing possible? If yes how. Secondly,
would this method offer significant speed as compared to my earlier
method. Thirdly are there other methods which might speed up this whole
process.

Regards,
HP
India