Excel2003 ...

My understanding is ... an array "Index/Match" Formula calculates faster
than a "VLookup" Formula ... If so, then I would like to use "Index/Match".
Also, I often do use "Index/Match" as the Index can be either R or L of
target cell.

Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to
copy all the way down the 2nd Col ... It seems to fail @ about 20,000 records
down the 2nd Col (one clarifier ... I am on the same WorkSheet ... adjacent
Cols)

Above said ... Am I up against an "Index/Match" constraint here ... or ...
as is generally the case ... Is this an oversight on my part?

Please enlighten me ... Thanks for the guidance ... Kha

Post your formula... there is no constraint on index match. That being said
index match is about 5% slower than VLookup...

http://www.decisionmodels.com/optspeede.htm

That being said index match is IMO the way to go for many reasons most
noteably it is far less prone to error than VLookup.

My best guess at your problem would have to with absolute vs relative
references.
--
HTH...

Jim Thomlinson


"Ken" wrote:

Excel2003 ...

My understanding is ... an array "Index/Match" Formula calculates faster
than a "VLookup" Formula ... If so, then I would like to use "Index/Match".
Also, I often do use "Index/Match" as the Index can be either R or L of
target cell.

Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to
copy all the way down the 2nd Col ... It seems to fail @ about 20,000 records
down the 2nd Col (one clarifier ... I am on the same WorkSheet ... adjacent
Cols)

Above said ... Am I up against an "Index/Match" constraint here ... or ...
as is generally the case ... Is this an oversight on my part?

Please enlighten me ... Thanks for the guidance ... Kha

I just setup an INDEX(MATCH formula, copied it down to row 28,347 and it
worked fine for me.

What is your formula?

--

HTH

Bob

"Ken" wrote in message
...
Excel2003 ...

My understanding is ... an array "Index/Match" Formula calculates faster
than a "VLookup" Formula ... If so, then I would like to use
"Index/Match".
Also, I often do use "Index/Match" as the Index can be either R or L of
target cell.

Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to
copy all the way down the 2nd Col ... It seems to fail @ about 20,000
records
down the 2nd Col (one clarifier ... I am on the same WorkSheet ...
adjacent
Cols)

Above said ... Am I up against an "Index/Match" constraint here ... or ...
as is generally the case ... Is this an oversight on my part?

Please enlighten me ... Thanks for the guidance ... Kha

Bob ... (Hi)

Did you copy your INDEX(MATCH formula down the 1st 40,000 rows before you
attempted to copy down the 28,347 rows?

Above said ... I have had this difficulty with INDEX(MATCH before & have a
note in my notes regarding this ... Consequently, in an effort to avoid I use
VLOOKUP when I run into this issue ... Otherwise, I use INDEX(MATCH because
as I previously stated I thought it to be a faster calculation & it had the
advantage of L & R return values??? After today though ... I am uncertain
which calculation may be faster???

My "Thanks" for supporting these boards ... There has been many lessons
learned here ... Kha

"Bob Phillips" wrote:

I just setup an INDEX(MATCH formula, copied it down to row 28,347 and it
worked fine for me.

What is your formula?

--

HTH

Bob

"Ken" wrote in message
...
Excel2003 ...

My understanding is ... an array "Index/Match" Formula calculates faster
than a "VLookup" Formula ... If so, then I would like to use
"Index/Match".
Also, I often do use "Index/Match" as the Index can be either R or L of
target cell.

Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to
copy all the way down the 2nd Col ... It seems to fail @ about 20,000
records
down the 2nd Col (one clarifier ... I am on the same WorkSheet ...
adjacent
Cols)

Above said ... Am I up against an "Index/Match" constraint here ... or ...
as is generally the case ... Is this an oversight on my part?

Please enlighten me ... Thanks for the guidance ... Kha




.