Index/Match vs VLookup?

[Ken]

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

 

[Jim Thomlinson]

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.

 

[Bob Phillips]

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

What is your formula?

 

[Ken]

Perhaps I misunderstood this older post (pasted @ bottom & in particular the
response to Question 2) ... but seems I have also seen other post that
indicated INDEX/MATCH to be a faster calculation than VLOOKUP ... The ironic
thing about the following post is that it indicates INDEX/MATCH to be faster,
but @ same time refers to the same website link you have in this post.

Above said ... Thank you for responding to my post & for supporting these
boards ... As far as INDEX/MATCH vs VLOOKUP calculation speed ... I guess it
is up to my particular spreadsheet & my Stop Watch ... ... Kha

****************************************************

Question 1: Yes, it would be more efficient. If the conditional test of the
IF function is TRUE it doesn't continue to evaluate the value_if_false. For
example:

=if(a1=0,0,formula)

If A1 = 0 it doesn't continue to calculate "formula".

Question 2: Yes, that will also help. Using a combination of INDEX/MATCH is
more efficient than VLOOKUP.

Visit this site for lots of info on efficiency:

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP

I've "inherited" a very complicated spreadsheet at work, which runs quite
slowly. I'm going to try to speed it up a bit. Here are some things I've
thought of so far, but I'm not sure if they're going to work.

1. In some places there are complicated formulae depending on many input
cells. But if one of those cells is zero then the formula will calculate
to
zero as well. Would it speed things up to replace "=formula" with
"=if(a1=0,0,formula). In other words, does the IF function calculate both
parts following the condition (in which case this would just slow it down
further) or does it only calculate the relevant part in which case this
might
save a lot of calculation time.

2. Imagine the first 1000 rows column A has various numbers which will
always be integers in the range 1-10 and column B has a complex formula
which
depends on the figure in the nieghboring cell in column A and also on
other
fixed cells in the spreadsheet. Would it speed up the spreadsheet to have
a
small ten row table somewhere in the spreadsheet with the complex forumla
worked out for numbers 1-10 and use and replace the formula in column B
with
a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a
complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")?

Are there any other good tips for speeding up spreadsheets?

Thanks,

Eric

 

[Ken]

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