Category Archives: Lookups

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

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 , , , , , , , | 6 Comments

FastExcel V3 Released with Introductory Offer

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

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

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

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

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

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

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

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

VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP

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

Posted in Calculation, Lookups | 33 Comments