Index/Match Cell Limitation?

[Ken]

Excel/2003 ... INDEX/MATCH array Formula (2 comparison) Formula working fine.

Issue ... Formula in 2 Cols each 40,000 Cells ... Formula Fills 1st Col, but
fails to fill beyond 25,000 cells (approx) of 2nd Col??? ... Have I reached
some sort of limitation here with this method?

Above said ... I originally used VLOOKUP & HELPER Col, but thought
INDEX/MATCH might be faster & would eliminate need for HELPER Col ... Now I
need help ...

Thanks ... Kha

 

[Ken]

J. (Good morning)

Version ... Excel2003

Memory:

Total Physical Memory …… 512.00 MB
Available Physical Memory . 158.80 MB
Total Virtual Memory …….. 2.00 GB
Available Virtual Memory … 1.96 GB
Page File Space ……………. 1.20 GB

Workbook Size ... 28mg:

5 Worksheets:

WS1 … 13 Cols x 40,000 Rows
WS2 … 20 Cols x 40,000 Rows
WS3 … 15 Cols x 40,000 Rows
WS4 … Pivot Table
WS5 … Pivot Chart

WS 3 Index/Match Formula filling 40,000 Rows of 1st Col, but only 25,000
Rows of 2nd Col before it fails to copy down.

Thanks … Kha

 

[JLatham]

I could easily be wrong, but I'm thinking that you've stretched the limits of
that system pretty much. That is a boatload of data do deal with. Probably
coming closer to the limits of the system resources than to actual physical
memory limits.

One way to test would be to take the file to another machine with more
memory (say a gigabyte or two at least) and see if you cannot complete that
second column of formulas on it.

 

[Ken]

J ... Thanks ...

I went back to VLOOKUP with a HELPER Col & I am getting the job done ...