Category Archives: Lookups

FastExcel Version 4 – with Introductory Offer

Posted on February 5, 2020 by fastexcel

After several man-years of development and a lot of beta testing FastExcel Version 4 has finally hit general availability! FastExcel V4 is a major rewrite of FastExcel V3 and provides many of the things you have been asking for: Support … Continue reading →

Posted in arrays, Calculation, Debug formulas, Dynamic Arrays, Excel, Lookups, Memory, UDF, VBA | Leave a comment

Stacking (Appending) Arrays/Ranges in Excel: Formulas versus Functions Shootout

Posted on January 16, 2015 by fastexcel

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 →

Posted in arrays, Calculation, Lookups, Uncategorized, XLL | Tagged , , , , , , , | 9 Comments

FastExcel V3 Released with Introductory Offer

Posted on June 10, 2014 by fastexcel

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 →

Posted in Calculation, Lookups, Memory, UDF, VBA, XLL | Leave a comment

Finding missing items in lists: VLOOKUP vs COMPARE.LISTS performance and ease of use

Posted on September 19, 2013 by fastexcel

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 →

Posted in Calculation, Lookups, UDF, XLL | Leave a comment

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 2

Posted on February 27, 2013 by fastexcel

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 →

Posted in Calculation, Lookups, UDF, XLL | Tagged , , , | 3 Comments

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 1

Posted on February 27, 2013 by fastexcel

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 →

Posted in Calculation, Lookups, UDF, XLL | Tagged | 2 Comments

The SpeedTools FILTER.IFS Function Design Part 3: Excel Data Types – Strange COUNTIF behaviour

Posted on January 18, 2013 by fastexcel

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 →

Posted in Calculation, Lookups, UDF, XLL | 5 Comments

The SpeedTools FILTERIFS function: Design and Implementation Part 1

Posted on December 21, 2012 by fastexcel

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 →

Posted in Calculation, Lookups, UDF, VBA, XLL | 2 Comments

Sorting and Comparing the Excel Way: Code Pages and Collating Sequences – Revised

Posted on June 9, 2012 by fastexcel

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 →

Posted in Lookups, UDF, VBA, XLL | 4 Comments

Vlookup tricks: the wild side of VLOOKUP

Posted on April 5, 2012 by fastexcel

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 →

Posted in Calculation, Lookups | Leave a comment