Vlookup or MatchIndex

[bjk]

We have delivery dates that we are breaking down into 13 periods and 52
weeks -each in a separate column.

Date Period Week

Aug 15,2000 1 3

I created a separate table with the periods and weeks broken down and
applied vlookup.
I do not totally grasp the MatchIndex concept and the owner says it is the
way to go. There are many sorts to this large spreadsheet and he feels that
the vlookup will lose the formula (??)
Is one way better than the other?
Sorry if this is a jumble.

Thanks

Brenda

[Tom Ogilvy]

If the lookup value is found in the left most column, Vlookup and the
combination of Index and Match are equivalent. You can use either.
[Vlookup with a fourth argument of False or 0 and Match having a third
argument of 0).

=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,FALSE)

=INDEX(Sheet2!$A$1:$F$200,MATCH(A1,Sheet2!$A$1:$A$200,0),2)

The above formulas are equivalent and I don't think either would have
different behavior if the source data is sorted. I did several random sorts
on the lookup table data and both functions continue to return the correct
value.

Regards,
Tom Ogilvy
MVP Excel

"bjk" <bren...@multiroute.com> wrote in message
news:CEH65.17160$qS3....@tor-nn1.netcom.ca...

[Rob Bovey]

<<The above formulas are equivalent and I don't think either would have
different behavior if the source data is sorted.>>

Hi Tom,

Just to add my 2c. I'm currently building a project that does a large number
of lookups on very large tables. I've had the idea in my head since Lord knows
when to stay away from the lookup functions because they were supposedly very
slow.

Well sometime between Excel 5, when I probably developed that notion, and
Excel 97, they've optimized the lookup functions such that they are now *very*
fast. In my tests, VLOOKUP beats the pants off of INDEX, never mind INDEX plus
MATCH (although granted, you can do some neat things with INDEX plus MATCH that
you can't with VLOOKUP).

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Tom Ogilvy" <Thomas....@hqda.army.mil> wrote in message
news:erCYFVd4$GA....@cppssbbsa02.microsoft.com...

[Niek Otten]

Hi Rob,

I also found that MATCH is slightly slower than VLOOKUP. INDEX however
operates almost at hardware addressing speed.
So if you need more then one item (column) from the same row, like in a
database, then one MATCH to find the row and several INDEXes to retrieve the
items is very fast.
Of course another nice feature of MATCH is that it accepts descending
tables.

With sorted tables (range_lookup parameter of VLOOKUP = TRUE, match_type of
MATCH = 1 or -1)), the size of the table is almost irrelevant. Even the
maximum size of 65536 hardly makes any difference.
If you need an exact match and the table is sorted, then from a performance
point of view it is better to ask for a non-exact match and check for the
match yourself. If done in one formula, that takes two lookups, but still is
considerably faster than an exact match.
With an extra cell to store the found value you don't even have the double
lookup.

Wit exact matches the speed also depends on the actual values: if the tables
are sorted and the values are found in the beginning of the table, then the
speed is higher. If they're not sorted one has to assume that in general
values that are present are found halfway and values that are not present
require looking through the entire table. One more reason to use sorted
tables whenever possible.

Finally, all kinds of error values, like #NA, take a lot of time. Therefore,
with sorted tables, if you check for the match yourself, include a lower and
an upper bound, so that no errors are generated.

I think it's high time for MS to introduce a whole new set of table
functions. The present set is not consistent and not complete.

1. Tables should be ascending or descending
2. Some functions (like MATCH) accept only single column tables, which means
you have to create seperate names for the first column. If you change the
table, that is a maintenance problem
3. There is no functionality for choosing between searching up to or up to
and including. Subtracting "a little" from the search key is a source of
errors.
4. There is no standard functionality for interpolation
5. There is no standard functionality for bidirectional search (like in the
lookup wizard)
6. There is no standard functionality for processing subsequent percentages
on one amount, like found in many tax systems

and so on.

I know there are many solutions offered (via these ng's or from MS), but I
really think they should be standard in Excel. And if you take the Lookup
wizard from MS for example, that doesn't work if you use the R1C1 addressing
system.

Groeten,

Niek Otten

Rob Bovey <Rob_...@msn.com> schreef in berichtnieuws
udnwsv$4$GA.244@cppssbbsa05...

[Bruce Girvitz]

I build a lot of databases with multicolumn information, primarily with
shipping rates by weight categories.
Match Index allows wildcards while vlookup does not. Match Index also chews
up a lot of memory. Vlookup does not support wildcards, file size gets
bigger, faster with vlookup, but does not chew up the memory as fast. Match
index would support a dynamic range, never tried it with vlookup, but I
would think it would not (assumes multi lookup values which would requite a
concatenation, usually in a hidden column of the database, and would
arbritarily be extended well past the last line of rate code to allow
additions)

In a nutshell, if both vlookup and match index are supported, match index is
more flexilble on small databases. The larger the database becomes, u will
be happier with Vlookup as it will return data quicker.

Bruce Girvitz

Rob Bovey <Rob_...@msn.com> wrote in message
news:udnwsv$4$GA.244@cppssbbsa05...

[Myrna Larson]

On Sun, 2 Jul 2000 13:52:12 +0200, "Niek Otten" <nico...@xs4all.nl> wrote:

Nice summary, Niek. I'd just like to offer a solution for this problem that
you mention:

>2. Some functions (like MATCH) accept only single column tables, which means
>you have to create seperate names for the first column. If you change the
>table, that is a maintenance problem

If the table multi-column is named Table, and you want to use MATCH on the 1st
column, for the 2nd argument you can write

OFFSET(Table,0,0,,1)

or

INDEX(Table,0,1)

With INDEX, if you specify 0 for the row, it means all rows, so the above
means "the first column of Table". Similarly, a column number of 0 means all
columns; INDEX(Table,5,0) means the entire 5th row.

You could use MATCH on, say, the 4th column with either

OFFSET(Table,0,3,,1)

or

INDEX(Table,0,4)

[exce...@my-deja.com]

Here is my 2c worth...

INDEX is a Volatile function in Excel 5 and 7 but non-Volatile in
97/2000 (Go figure). So if you have a large number of them in Excel 5
or 95/7.0 then VLOOKUP will naturally be faster. However in Excel
97/2000 you would be better of using one MATCH (sorted) a Boolean check
for Exacts and using INDEX as much as possible. That way you can get
the most out of the what you are after.

INDEX is very fast, and so when using one MATCH and several INDEX's in
a row of data, this will be much faster than several VLOOKUP's on the
same row.

FYI. Volatile functions are NOW, RAND, (INDEX), INDIRECT, OFFSET,
CELL, INFO, and any User defined functions set to Volatile.
Also both MATCH & VLOOKUP use a Linear search, so reducing table sizes
can improve speed. This can be achieved by using Hash-Matching, but if
you are wanting to use this kind of technique to improve your
performance in Excel, maybe you should think about using a diffrent
solution than Excel as it was never really optimised for large
quantities of data or big tables....( apart from the fact that just
about all users of Excel use it for this - again go figure)

Cheers

In article <395f6...@news.cadvision.com>,

Sent via Deja.com http://www.deja.com/
Before you buy.

[Niek Otten]

The linear search happens only in the case of exact match. With non-exact
match Excel uses some kind of cleverer search, which is possible beacuse the
table is (has to be) sorted.

The fact that a table is sorted should really be a property of the table,
not a parameter for the search. Exact matches on sorted tables could be fast
as well.

Groeten,

Niek Otten

<exce...@my-deja.com> schreef in berichtnieuws
8jr2r5$7gq$1...@nnrp1.deja.com...

[Niek Otten]

Hi Myrna,

Thanks!

I must admit it took me some time to fully understand your solutions. For
some unknown reason I like the INDEX solution most.
They are beautiful! I had developed some alternative solutions using
intersection with the addresses or the first column; much too complicated.

For all the shortcomings of the table-functions I mentioned (not to mention
those I didn't mention) there is of course a solution. But I still think
that spreadsheets should be simple and easy to understand for the occasional
user too. Table-functions in Excel, in my opinion, aren't.

This thread started with performance issues. So I couldn't help testing your
suggestions. I set up 25000 formulas to process a 25000 rows, 3 column
table, in 3 variations:

1. seperately defined name for the first column (my initial solution),
2. your OFFSET solution
3. your INDEX solution

the outcome:
1. 175 seconds (Yes, I know I have a slow machine)
2. 176 seconds
3. 176 seconds

So the overhead of your solutions is totally negligible

Thanks again,

Groeten,

Niek Otten

Myrna Larson <myrna...@home.net> schreef in berichtnieuws
qlm1ms0i0gf42sc55...@4ax.com...

[Myrna Larson]

On Wed, 5 Jul 2000 17:25:38 +0200, "Niek Otten" <nico...@xs4all.nl> wrote:

Thanks for taking the time to do the timing. I'm always interested in that
aspect of things.

>1. separately defined name for the first column (my initial solution),

[student]

Myrna Larson <myrna...@home.net> wrote in message
news:qlm1ms0i0gf42sc55...@4ax.com...
> On Sun, 2 Jul 2000 13:52:12 +0200, "Niek Otten" <nico...@xs4all.nl>
wrote:
>