Not able to complete the task?

[Eric]

Does anyone have any suggestions on my case?
When I use Index function as shown below,

=INDEX(''!O:O,MATCH($B3,'path'!$A:$A,0))
=INDEX('path'!O$2:O$10000,MATCH($B3,'path'!$A$2:$A$10000,0))
=INDEX('path'!O$8000:O$9000,MATCH($B3,'path'!$A$8000:$A$9000,0))

Excel is not able to complete the task with existing resource, please select
less data or close other application.

Does anyone have any suggestions what cause the problem?
Thanks in advance for any suggestions
Eric

 

[Shane Devenshire]

Hi,

which of these functions is causing the problem? And what does !O:O mean?
Is it necessary to Match against the entire 65,536 rows or 1,048,000 rows
(2007)? That is very resource intensive.

Thanks,
Shane Devenshire

 

[Eric]

Thank you very much for suggestions

I get the same problem with limited range for following code too.
=INDEX('path'!O$8000:O$9000,MATCH($B3,'path'!$A$8000:$A$9000,0))
I get no problem, if the path is with the same worksheet, but if the path is
on another worksheet, the problem ocurred.

Do you have any suggestions?
Thank everyone very much for any suggestions
Eric

 

[Shane Devenshire]

Hi Eric,

Yes, going against a closed external file is much slower than working
against another range in your workbook, (and probably a lot slower than if
you opened the other workbook - so you might try opening the other workbook
and see if that improves things.)

I suspect that Microsoft would say 1. close all other applications, 2.
increase RAM, 3. increase free hard disk space, and maybe increase swap file
size.

Although VLOOKUP is also resource intensive it looks to me as though you
could use

=VLOOKUP($B3,'path'!$A$8000:$O$9000,15,FALSE))

You will need to test to see if this improves things.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Eric]

Thank you very much for suggestions
VLOOPUP does not work too for my case.
Does anyone have any suggestions? or it will be the limitation for Excel
Thank everyone very much for any suggestions
Eric