RagDyer wrote:
I guess I should apologize for again not being too clear (feel like a novice
OP).

There are approx. 80,000 lookup (Index - Match) formulas in this WB, 2 for
each record, where the records are all usually closed, and spread across 2
servers.
I mentioned the fact that there are 56 columns, since almost each is linked
directly to various cells (data) contained in each record (dyeorder), and
this linking contributes heavily to the open and recalc times in the WB.

I realized the reduction in opening time from just revising the lookup
formula from Vlookup to Index & Match.

And I'm repeating what I posted here a couple of weeks ago.
I made this revision after reading one or YOUR posts, a couple of years ago,
in which you stated a definite efficiency advantage in using Index & Match
as opposed to error trapping double Vlookups.
--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Aladin Akyurek" wrote in message
...

Is it not possible to set the 40,000 x 56 area in ascending order and
maintain in the sorted state?

RagDyeR wrote:

I was not exactly explicit enough when I stated that I found that Index

&

Match was much faster then Vlookup.

To be more precise, in a 40,000 row by 56 column WB, accessing closed

WBs on

different drives (servers), a double Vlookup (error trapping) formula,

with

0 as 4th argument, was replaced with a Match + Index & Match (error
trapping) formula, with 0 as the 3rd argument.

The opening time of the original WB was just under 5 minutes.
With the revised Index & Match formulas, the time was reduced to just

under

3 minutes.
Recalc times went from 1½ minutes to just under a minute.

To me, that's "much" faster.<g




RD,

I'm aware of that as the reason can be gleaned from:

http://www.mrexcel.com/board2/viewtopic.php?t=40233

You didn't respond to:

"Is it not possible to set the 40,000 x 56 area in ascending order and
maintain in the sorted state?"

If the answer would be yes, you can really resort to a significantly
faster formula. <g

For example:

Let A1:B11 house:

{"Item","Value";"DAX",5;"GDA",7;"MNA",4;"NGA",9;"P AX",3;"PDA",8;"QWA",6;"SAP",8;"TRA",2;"XZA",5}

Let also D3:D9 house the following lookup values:

{"DAX";"MNA";"MIA";"PDA";"PKA";"SQA";"SAP"}

Ordinarily we invoke in E3 and copy down...

=VLOOKUP(D3,$A$2:$B$11,2,0)


If we can sort and maintain A2:B11 in ascending order on its
match-range, it's significantly faster to invoke in E3 and copy down:

=IF(LOOKUP(D3,$A$2:$A$11)=D3,LOOKUP(D3,$A$2:$B$11) ,"")

I picked up this setup from Charles Williams, which, I have to admit, is
also faster than a setup I devised (even published).

If you also add the following as the first record to the table:

=CHAR(1), =""

the LOOKUP formula won't turn up any #N/A, while the first one with
match-type set to 0 will need something like:

=IF(ISNUMBER(MATCH(D3,$A$2:$B$11,0)),VLOOKUP(D3,$A $2:$B$11,2,0),"")

to avoid #N/A's.