Category Archives: Lookups

This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. … Continue reading →

FastExcel has been used successfully by thousands of users since it was first launched in 2001. The last version 2.4 was introduced in 2008 and since that time there have been major changes to Excel with Excel 2007, 2010 and 2013, … Continue reading →

Returning to the subject of finding the missmatches between 2 lists I want to compare using VLOOKUP with using SpeedTools COMPARE.LISTS. Test Data My test data consists of 2 lists of 500000 6-digit numbers. Most of these numbers match, but … Continue reading →

In part 1 I looked at how FastExcel SpeedTools MEMLOOKUP and AVLOOKUP2 compared to VLOOKUP and INDEX/MATCH for ease of use and power. This post will benchmark the performance of the SpeedTools lookups against the standard Excel functions. Download the … Continue reading →

Its time for some performance tests to see how the new functions in SpeedTools stack up against the standard Excel functions. First up is MEMLOOKUP and AVLOOKUP2 vs VLOOKUP! SpeedTools Lookups are easier to use, more powerful and less error … Continue reading →

The previous post discussed how Excel’s data types, and how FILTER.IFS was designed to handle them. Colin Legg suggested that a good starting point for the design choices could be what COUNTIF/SUMIF do. So here is an example of some … Continue reading →

Excel users have been using SUMPRODUCT and array formulas to create multiple-condition formulas for many years. This is a powerful technique, but can be painfully slow with large amounts of data. Pivot Tables and Excel 2013’s PowerPivot can provide good … Continue reading →

Comparing 2 values should be easy, but … Comparing values to see which is greater should be straightforward: you just use the less-than comparison operator: IF(A1<A2,TRUE,FALSE). So 1 is less than 2 and A is less than B. And when … Continue reading →

Here is another VLOOKUP post for Bill Jelen’s (now extended) VLOOKUP week. VLOOKUP Week – Brought to You by Mr. Excel (Click Image for the Official Site of VLOOKUP Week) Here is a little-used wild VLOOKUP trick that can sometimes … Continue reading →

OK, time for a post on VLOOKUP for Bill Jelen’s VLOOKUP week. VLOOKUP Week – Brought to You by Mr. Excel (Click Image for the Official Site of VLOOKUP Week) For all you VLOOKUP junkies who can’t get enough of … Continue reading →