Hi RD,
Indeed with 4th argument FALSE, the search stops as soon as the value is
found. If it just isn't present, that means the whole table has to be
searched.
With a sorted list, necessary to be able to establish the closest match,
very fast search methods are possible and are indeed used by Excel.
It would be nice if you could instruct Excel to assume a sorted list,
although you require exact matches. Many people have built UDFs to do just
that.
10.000 VLOOKUPS in a 65000 elements table, value to be found somewhere in
the middle:
4th argument FALSE: 28 seconds. Value not present: 53 seconds.
4th argument TRUE: .032 seconds
INDEX and MATCH: .015 seconds
--
Kind Regards,
Niek Otten
Microsoft MVP - Excel
"RagDyeR" wrote in message
...
Actually Niek, I believe that the False (0) argument *stops* at the
*first*
exact match, while the True (1) argument causes the *entire* range to be
searched.
Did you actually find False to be slower then True?
Also, from personal experience, Index & Match are *much* faster then
Vlookup.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
"Niek Otten" wrote in message
...
I used FastExcel on a sheet with which I had recalc tome problems.
It helped me spot the problem area in minutes and then it didn't take much
time (in my case!) to cure the problem.
Summing large ranges is generally very, very fast. A helper column and a
normal SUM are probably faster than SUMIF.
Your problem might be the VLOOKUP. You probably use FALSE as 4th argument.
This causes Excel to search the range from top to bottom. If you can sort
the data and omit the 4th argument, you may find a great reduction in
calculation time. But you'll have to check yourself that the value to be
searched was indeed the one found (to check for missing data)
--
Kind Regards,
Niek Otten
Microsoft MVP - Excel
"Naz" wrote in message
...
Hi all,
I have a spreadsheet that I have been using for the year and now it is
time
to update it. The biggest problem with the old one was the re-calculate
time.
I have 2 worksheets in the workbook, 1 containing data and the other a
List
of employees (over 500). The List sheet picks up the pay of each staff
from
the data sheet every month. I used to use an array formula to do this
then
used a conjoined sumif (Sumif(Range,PayRef&Month&Costcode,Sumrange) with
helper column as it was faster, but as the Data sheet gets bigger as the
years goes on the recalculate time is now at 12 secs.
Is there a faster alternative to Sumif?
Is there better method of summing large ranges of data (data sheet
contains
14000 rows)?
Has anyone used the product FastExcel (www.fastexcel.com) to optimise
there
spreadsheets and is it any good?
All help is greatly ppreciated
_______________________
Naz,
London