To Vlookup or Not - that is the question.

 This ties a little to zfraile's post on faster lookups but most definatlely applies ONLY to unsorted lists. In this environment specifying a sorted list is not an option.

A lot of our workbooks use Vlookups extensivley but the recalculation times are starting to suffer. So I started combining  MATCH with INDEX figuring that:

  Using the match once (in a helper column) to find the row and then use the index repeatedly to point to a single cell just has to be faster - right?

Nope - visually this appears to have virtually no effect and using Charles Williams FastExcel confirms this.

Is the time difference negligable because the MATCH is recalculated each time INDEX refers to it?

Is there a faster way of looking up against unsorted lists?

If there's a simple, elegant and fast solution then a lot people here would be very happy!!

I think what this really confirms is that the biggest variable in lookup times is the dataset, because my results are very different.

I used a dataset of 22,703 rows and 38 columns.  (I don't know if the columns are important in any way other than it would affect the amount of RAM used by Excel).  The lookup is being applied against five of those columns where the lookup value is a number, two of the result columns are dates, and two are text values.  It is sorted by one of the text values, which gives the lookup value a fairly random order.

Test 1: 4 vlookups: total time = 80 seconds

Test 2:  1 vlookup: total time = 21 seconds

Test 2b:  a second vlookup next to the first = 21 seconds (to see if subsequent lookups take longer)

Test 3:  1 match and 4 indexes = 20 seconds

Test 4:  1 match, no indexes = 20 seconds

Test 4b:  4 indexes based on the completed match = < 1 second

Test 4c:  4 indexes based on the completed match where the match is pasted as values = < 1 second

I don't know if the 1 second difference between test 2 and 4 is evidence enough that the match function is faster than the single vlookup.

What's interesting is that the difference between test 3 and 4 is virtually zero, which means the indexes add almost no noticeable calculation time.  This is confirmed by test 4b.  There's no way the match is recalculating based on the reference by the index.  Text 4c confirms this.

The difference between Test 1 and 4, which give exactly the same result, is a full minute.

Based on this, I'd say the match function wins.

Here's something else.  I tried test 4 again, but this time I sorted the dataset by the number field (which basically randomizes the first text field), then did the match against the first text field.  The result?  14 seconds.  I then repeated test 2 matching against the text field for a total of 13 seconds.  So in this case the vlookup beat the match.  I wasn't bored enough to see if these results scale proportionally on much larger datasets, but I wouldn't be surprised to see the results all over the place.

So it definitely depends on the data in question.  That said, I can't imagine a situation where one match and multiple indexes would not beat multiple vlookups.

Elegant and simple just seem to be out of reach with Excel lookups.  I think that's an area that gets a lot of use and has been sorely overlooked in optimizing Excel, especially because a lot of that is ad-hoc usage and even when you can remember the complicated syntax for an index match on a sorted list, it's usually not worth the hassle to save a minute or two of looking at the status bar.

I'll point to a program called Monarch from Math Strategies to prove how weak Excel is.  I use this program to combine three reports based on a matching account number.  Each of these reports has about 150,000 rows of data.  What I use Monarch for is to convert text reports into Excel files.  So first I convert two of the reports to Excel.  Then I open up the third report in Monarch and use its lookup feature to append the data from the first two files, now in Excel format.  Total processing time to do the match?  About 2 seconds.  The numbers are not sorted in any way.  Total time to do the same in Excel using Match?  About 5 minutes.  The total time to do the same in Excel using match if I sort the data first is about 20 seconds.  I think Ballmer needs to take some of that newly freed Yahoo! money and buy himself this company.  Or at least pinch some of their engineers.

Finally got to taking a look at this.

I was making a slight mistake when evaluating the combinations.

For an equal number of matches and indexes (12 indexes with 12 matches) the overall calc time is little different to 12 lookups. this is where I was looking. However, the time gets progressively better as, say, 12 matches are used to feed 36 indexes. Test 3 vs Test 1 above.

So MATCH + INDEX it is when there are lots to 'lookup'!