Cat On A Spreadsheet

One of the most useful things we can do with a dynamic array is to take a collection of records and add information to it from somewhere else. In a traditional Excel report, this usually means adding a lookup column to the source data, filling the formula down, and then perhaps building another formula around the result. There is nothing wrong with that approach, and functions such as XLOOKUP have made it considerably cleaner than the old VLOOKUP and INDEX/MATCH combinations. But once dynamic arrays enter the picture, we can start thinking about the problem differently. Instead of asking Excel to perform a lookup once for every row, we can ask it to take an entire collection of values, find the corresponding information for all of them, and return the resulting collection as a single array.

That distinction is more important than it might initially appear.

Suppose we have a sales table containing transactions, but the transaction data contains only a product code. The product description, category and standard price are maintained elsewhere. A conventional report might have a column for the product code, another column containing an XLOOKUP for the description, another for the category, and perhaps another for the price. With dynamic arrays, we can instead regard the transaction table as the dataset we are working with and the product table as an enrichment source. We first select the records we need, extract the key that identifies each record, retrieve the corresponding information, and then combine the original information with the retrieved information. This is the same general pattern we have been developing throughout this series. We start with an array, transform it, and return another array. The lookup is simply another transformation.

Consider a simple product table:

Our transaction data might contain nothing more than the transaction number, date, product code and quantity:

The report might need to display the description, category and unit price alongside each transaction. The interesting part is that the transaction table does not contain those fields. They have to be retrieved from the product table.

The simplest version of the formula is almost embarrassingly small:

The important thing here is not the syntax. It is what XLOOKUP is receiving as its lookup value. Instead of giving it a single cell such as C2, we give it the entire range C2:C5. XLOOKUP therefore returns an array containing the description corresponding to each product code. If the product codes are P002, P004, P001 and P003, the result is an array containing Mouse, Laptop, Keyboard and Monitor, in precisely that order.

This is one of the simplest examples of an existing Excel function becoming significantly more powerful when we stop thinking in terms of individual cells and start thinking in terms of arrays. The same principle applies if we want several columns from the lookup table. Suppose the product table is stored in an Excel Table called tblProducts, with columns named ProductCode, Description, Category and Price. We could retrieve several fields at once:

Because the return array contains three columns, the result spills into three columns. One lookup operation has therefore enriched the entire transaction dataset with three additional fields.

This is a useful point at which to introduce a subtle but important distinction between looking up a value and looking up a record. When we use XLOOKUP with a single return column, we are asking for one attribute of the matching record. When we give it a multi-column return array, we are effectively asking it to return part of the matching record. That makes XLOOKUP considerably more useful as a component of dynamic-array reporting than its reputation as merely a replacement for VLOOKUP might suggest.

But there is an even more interesting situation. Suppose we do not want to add the lookup result to the worksheet beside the original data. Instead, we want to construct a complete report from a filtered subset of the transactions. This is where the techniques from the previous posts begin to connect.

Imagine that we want all transactions for a particular date, but we want the resulting report to contain the transaction number, date, product description, category and quantity. We can first filter the transaction data and then use the resulting product codes as the lookup values. With LET, the logic becomes much easier to follow:

There is quite a lot happening here, but the underlying process is straightforward. First, FILTER creates the transaction dataset we actually want to report. We then extract the product-code column from that filtered array using CHOOSECOLS. That gives XLOOKUP precisely the keys it needs. The lookup returns the description and category for every matching transaction, and finally HSTACK places those new columns alongside the original transaction data.

The important thing is that none of those intermediate arrays need to exist on the worksheet. They exist inside the formula.

This is where LET becomes more than a cosmetic improvement. It allows us to give names to the intermediate datasets and therefore express the formula in terms of the transformations being performed rather than forcing the reader to mentally decode a deeply nested expression. It also means that the same filtered dataset can be reused. If we wanted to add the unit price as well, we would not need to repeat the filtering operation. We could simply perform another lookup against the already-created Codes array.

There is another approach worth considering, however. If we know that all three pieces of information come from the same record in the product table, we do not necessarily need three separate lookups. We can return all three columns in one operation:

This is often preferable because the lookup operation itself now corresponds neatly to the business concept: find the product records associated with these transaction codes.

There is a catch, though, and it is one that becomes increasingly important as formulas become more sophisticated. The lookup key must identify the correct record. That sounds obvious, but reporting data often contains keys that are not genuinely unique. A product code might be unique in a product master, but a customer name certainly may not be unique in a customer table. An employee's surname is not a suitable key. A date is almost never a suitable key by itself. Even an apparently unique identifier can become ambiguous when data from different systems is combined.

Dynamic arrays do not remove this problem. They merely make it easier to perform the lookup across a large dataset.

Sometimes the correct key is actually a combination of fields. Suppose a sales table contains an employee number and a department code, while the reference table identifies an employee by that same combination. We can construct a composite key in both datasets. For example:

The concatenation creates an array of composite keys. XLOOKUP can then compare that array against another array of composite keys.

This is a useful technique, but it should be used deliberately. If the underlying data model provides a proper unique identifier, that identifier is preferable.  Constructing keys from text fields can introduce problems with inconsistent formatting, delimiters appearing inside values, or changes to the source data.

There is also a question of what should happen when the lookup fails. For a polished report, simply returning #N/A may not be desirable. XLOOKUP allows us to specify an alternative result:

But "Unknown product" is not always the best answer. In some reports, a missing lookup should be treated as a data-quality problem rather than silently presented as a legitimate result. This distinction matters because a dynamic report can make bad source data look deceptively clean.

If a transaction contains product code P999, and the formula simply returns "Unknown product", the report still produces a perfectly valid-looking row. The missing reference data may therefore go unnoticed. A better reporting design might deliberately expose the exception:

Here, an unmatched code remains visibly erroneous. Whether that is desirable depends entirely on the purpose of the report. A management report might want a friendly label, whereas a data-quality report should probably expose the missing key.

This is one of the broader themes behind dynamic-array reporting: the formula is not merely a technical solution. It is part of the report's design.

There is another particularly useful variation when the lookup table itself needs to be filtered. Imagine that we have a large product master containing historical products, discontinued products and products belonging to different divisions. The report should only be allowed to retrieve products belonging to the current division. Rather than blindly looking up against the entire master table, we can create the valid lookup dataset first:

Now the lookup is being performed against a dynamically generated reference array.

Once again, the important idea is that FILTER, XLOOKUP, CHOOSECOLS and LET are not isolated tricks. They are components that can be assembled into a data-transformation pipeline. We can filter a dataset, extract its keys, look those keys up against another dataset, select the attributes we need, and combine everything into the final report. That is a very different way of thinking about an Excel formula. Instead of asking, “What formula goes in this cell?”, we can ask, “What data should this report contain, where does each piece of information come from, and what transformations are necessary to produce it?” Once we start asking those questions, the boundary between a worksheet formula and a small reporting system becomes surprisingly thin.

There is one final consideration worth making before moving on. XLOOKUP is exceptionally useful when we have a key and need to retrieve attributes from another dataset, but sometimes we do not merely want to enrich individual records. We want to understand the relationship between datasets. For example, perhaps we want to know how many transactions each product has generated, what the total quantity sold was, or which products have never appeared in the transaction data at all. At that point, lookup is no longer the main operation. We are moving towards grouping, aggregation and ranking. And that is where the next stage of dynamic-array reporting becomes considerably more interesting. The lookup has given us the ability to enrich our dataset. The next question is what we can do once that enriched dataset exists.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet