Maximum size of Match (,array)?

[eibi]

Everybody:

I'm trying to execute an Index(,Match) operation -- the lookup_array is 100,000 rows of data...sorted ascending.

But my match function seems to fail after testing row 87,500...So if the lookup_value happens to be on the 87,501st row (or later), the match function automatically returns a value of 100,000.

Perhaps I'm bumping into an upper limit on Excel's index/match memory, but I can't find much guidance on this in my websearches...

Any related knowledge or suggestions for a workaround?

 

[vletm]

@eibi
Could You do Your Index(,Match) operation with two or more parts 2* 50'000?

 

[Hui]

It will depend on the version of Excel you are using

Newer versions will be limited by Memory

 

[Hui]

If it was going to break I would have expected it to break around the 65,000th row

So also check the data that is in the 87,501st row, it could be the cause of the issue

Can you try it on a PC with more memory?