Spreadsheet Design Best Practices

📘

Business Users that write Pebble Stream enabled spreadsheets are called Pebblers.

Pebblers are Pebble Stream savvy spreadsheet designers. We think Pebblers are first-class software developers and you should too!

Avoid using VLOOKUP and HLOOKUP

VLOOKUP is a popular spreadsheet function. It is used to lookup up a value based on a provided criteria. We won't get into how VLOOKUP works. Instead, we will point out why we do not advise its use.

The use of VLOOKUP leads to brittle worksheets
Pebblers need to specify a contiguous range for VLOOKUP to operate. To retrieve the searched data item, pebblers must hardcode a numeric offset as one of VLOOKUP's parameters. Using VLOOKUP leads to worksheets that pebblers cannot change without breaking the VLOOKUP logic.

Exact match runs in linear time
Pebblers may choose VLOOKUP to use exact match semantics to find the search key in the specified cell range. When pebblers apply VLOOKUP with the exact match option specified, VLOOKUP will compare every value in the specified cell range until matching the provided search key. Using VLOOKUP in this manner is highly inefficient and can lead to the creation of very slow worksheets.

Approximate match is hard to use
Pebblers may choose VLOOKUP to use approximate match semantics to find the search key in the specified cell range. When pebblers apply VLOOKUP with the approximate match option specified, the cell range over which the specified search key is applied, the pebbler must have already sorted the cell range in the proper order. If the pebbler does not sort the range correctly, the approximate match call may fail. Depending on the cell range, the approximate match call may even return a value that is not equal to the value of the provided search key.

Previous searches made using VLOOKUP cannot be reused
VLOOKUP hides the position information it gained when searching for a key in a range. Without access to the search key's position information, it is impossible to reuse the search key position information to avoid performing another lookup.

Everything we said about avoiding VLOOKUP applies to the HLOOKUP function as well.

👍

Prefer the use of INDEX MATCH over VLOOKUP, or better yet, use a relational directive instead.

Instead of using VLOOKUP, use the functions INDEX and MATCH. Or even better, ditch lookup functions altogether and use Pebble Stream's cartesian-product, join, or outer-join directives instead.

If you must lookup a value using INDEX MATCH

Pebblers should use the INDEX and MATCH functions to perform data lookups, instead of using VLOOKUP or HLOOKUP. Unlike VLOOKUP, INDEX and MATCH allow for caching of search key position (via MATCH), while both the INDEX and MATCH functions are highly amenable to changes in worksheet layout without suffering ill effects.

Like VLOOKUP and HLOOKUP, the MATCH function suffers from the same limitations experienced when using the exact match and the approximate match capabilities of VLOOKUP.

Although a better choice than VLOOKUP, the INDEX and MATCH functions are still less than ideal in their behavior. In many situations, it is better to use Pebble Stream's relational directives instead.

👍

Prefer the use of relational directives over lookup functions whenever possible.

Relational directives are easier to understand and use, less prone to surprising behavior, and typically perform better than lookups.

Choose a relational directive over lookup functions whenever feasible

The relational transformative directives join, cartesian-product, and outer-join are often better alternatives to lookup functions.

In short, relational directives are easier to understand and use, less prone to surprising behavior, and typically perform better than lookups.

Never use the functions INDIRECT or OFFSET

INDIRECT and OFFSET are two functions that, when used at all, lend to the creation of worksheets that pebblers cannot easily change. Formulas that contain INDIRECT and OFFSET are complicated for users to understand. The Pebble Stream runtime cannot optimize worksheets containing INDIRECT and OFFSET functions.

Instead of using INDIRECT or OFFSET, consider the INDEX function as a sound alternative.

❗️

Pebble Stream will not support INDIRECT and OFFSET in future releases.

Consider using INDEX, or rely on Pebble Stream's relational directives instead.