Data Organization, Tables, Lookups, and Dynamic Arrays

Introduction

Business analysis begins with data structure. A technically sophisticated formula cannot compensate for poorly organized source data. In Excel, reliable analysis depends on maintaining a clear separation between raw records, reference data, calculations, and reports. This chapter develops a practical framework for organizing datasets as tidy Excel Tables and then using sorting, filtering, structured references, lookup functions, and dynamic arrays to produce reusable analyses.

The central principle is that a spreadsheet should represent business events consistently. Each row should normally describe one observation or transaction, each column should represent one variable, and each cell should contain one value. This structure supports accurate filtering, aggregation, integration, auditing, and automation.

Learning Objectives

By the end of this chapter, you should be able to:

• Design a tidy dataset suitable for Excel analysis.
• Convert a range into an Excel Table and use structured references.
• Sort and filter records without damaging the underlying dataset.
• Select appropriate lookup methods, including XLOOKUP and INDEX–MATCH.
• Use FILTER, SORT, UNIQUE, SEQUENCE, and related dynamic-array functions.
• Integrate transaction data with reference tables.
• Diagnose common errors involving duplicates, missing values, data types, and spilled arrays.
• Apply professional standards of data governance, documentation, and reproducibility.

1. Data Organization as an Analytical Foundation

A business dataset is often described as a relation: a collection of records sharing a common schema. For example, a sales table may contain one row per order line and variables such as OrderID, OrderDate, CustomerID, ProductID, Region, Quantity, UnitPrice, and SalesAmount.

A tidy dataset follows four practical rules:

1. Every variable has its own column.
2. Every observation has its own row.
3. Every cell contains one value, rather than multiple values separated by commas or line breaks.
4. Variable names are unique, stable, and meaningful.

A suitable sales table might appear as follows:

A calculated SalesAmount column could use the relationship:

Here, Quantity is measured in units, UnitPrice is currency per unit, and SalesAmount is currency. The relationship assumes that discounts, taxes, returns, and shipping costs are either irrelevant or handled in separate variables. In a real organization, the definition of revenue must be agreed before formulas are created.

1.1 Keys and Relationships

A key is a field, or combination of fields, used to identify a record. An OrderID may identify an order, but it may not uniquely identify each product line within that order. In that case, a composite key such as OrderID plus ProductID may be required.

A primary key uniquely identifies a record in its own table. A foreign key stores a value that refers to a key in another table. For example, ProductID may be a primary key in a Products table and a foreign key in a Sales table.

Lookup accuracy depends on key quality. A lookup key should be:

• complete rather than missing;
• consistently formatted;
• unique in the lookup table when one result is expected; and
• free from unintended spaces, inconsistent capitalization, or incompatible data types.

A useful relational design separates entities. Instead of repeating product descriptions and categories in every transaction row, maintain a Products table and connect it to Sales through ProductID. This reduces inconsistent edits and supports controlled updates.

2. Excel Tables

An Excel Table is a named, structured range with built-in filtering, automatic expansion, calculated columns, and structured references. To create one, select the dataset and choose Insert → Table. Confirm that the range has headers, then assign a meaningful name such as tblSales.

Tables are preferable to ordinary cell ranges for most recurring analyses because formulas can refer to field names rather than fragile coordinates. For example:

=[@Quantity]*[@UnitPrice]

This formula calculates SalesAmount for the current row. A total sales formula can be written as:

=SUM(tblSales[SalesAmount])

A conditional total can be written as:

=SUMIFS(tblSales[SalesAmount],tblSales[Region],"North")

The notation tblSales[SalesAmount] is a structured reference to the entire column, while [@SalesAmount] refers to the current row. Structured references are readable and generally continue to work when rows are added.

2.1 Table Design Standards

Good table design includes:

• one header row only;
• no blank rows or columns inside the data body;
• consistent data types within each column;
• dates stored as genuine Excel dates, not text that merely looks like a date;
• numeric fields stored as numbers rather than currency symbols embedded in text;
• no merged cells in the data area;
• descriptive table and column names;
• separate fields for separate concepts, such as FirstName and LastName rather than one combined field when both are analytically relevant.

A table should contain source data and stable row-level calculations, not decorative subtotals or manually inserted report headings. Reports should be built separately from the table.

2.2 Sorting and Filtering

Sorting changes the displayed order of records. Filtering hides records that do not meet selected criteria. Neither operation should be confused with deleting data. Filtering is especially useful for investigating exceptions, such as sales above a threshold or orders from a particular region.

Sort criteria should be explicit. A multi-level sort might order records first by Region ascending and then by SalesAmount descending. Analysts should avoid sorting only one column in a way that disconnects values from their records. Sorting the entire Table preserves row relationships.

Filters may be applied by value, text, number, date, or color. Although convenient, color-based filtering is less transparent than criteria-based filtering and may be difficult to audit.

3. Structured References and Reusable Calculations

Structured references improve readability and reduce dependence on fixed coordinates. Consider a table named tblSales with columns Quantity and UnitPrice. A calculated column can use:

=[@Quantity]*[@UnitPrice]

A report formula can use:

=SUMIFS(tblSales[SalesAmount],tblSales[Region],$B$2,tblSales[OrderDate],">="&$B$3,tblSales[OrderDate],"<="&$B$4)

This formula sums SalesAmount when Region equals the value in B2 and OrderDate lies between B3 and B4. The criteria operators are concatenated with cell references. The date cells must contain valid Excel dates, and the inclusive date interpretation should match the reporting requirement.

Structured references have limitations. They can become difficult to read when formulas contain many criteria, and table names must be managed carefully when workbooks are shared. Nevertheless, for recurring business workbooks they are usually more maintainable than references such as $G$2:$G$50000.

4. Lookup Methods

A lookup retrieves information associated with a key. For example, a sales transaction may contain ProductID, while the product category is stored in tblProducts. A lookup adds the category to the transaction or report.

4.1 XLOOKUP

The modern general-purpose function is XLOOKUP:

=XLOOKUP([@ProductID],tblProducts[ProductID],tblProducts[Category],"Not found")

Its arguments are, conceptually:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

XLOOKUP can search horizontally or vertically, return values to the left or right of the key, specify a missing-value result, and support exact or approximate matching. Exact matching is the default and is normally appropriate for identifiers.

For a two-way lookup, XLOOKUP can be nested. If B2 contains a product ID and B3 contains a field name, a formula might be:

=XLOOKUP(B2,tblProducts[ProductID],XLOOKUP(B3,tblProducts[#Headers],tblProducts))

This approach should be used cautiously because it depends on header names being exact and unique.

4.2 INDEX–MATCH

INDEX–MATCH is an established alternative that remains valuable in compatibility-sensitive workbooks. A basic formula is:

=INDEX(tblProducts[Category],MATCH([@ProductID],tblProducts[ProductID],0))

MATCH returns the position of the key, and INDEX returns the value at that position. The final argument 0 requests exact matching. Approximate MATCH modes require sorted data and have different semantics; they should not be used casually for identifiers.

INDEX–MATCH is useful when analysts need independent control over the lookup and return ranges, or when a workbook must work in Excel versions that do not support XLOOKUP. Its principal weakness is greater syntactic complexity and the possibility of mismatched range sizes.

4.3 Choosing a Lookup Method

The correct method depends on cardinality. If one key should return one attribute, use XLOOKUP or INDEX–MATCH. If one criterion should return many records, use FILTER. If the data must be repeatedly imported, cleaned, and combined, Power Query may be more appropriate than worksheet formulas.

5. Dynamic Arrays

A dynamic-array formula can return multiple values that spill automatically into neighboring cells. The formula is entered once in the top-left cell of the output area. Excel then populates the spill range.

A spill range must be unobstructed. If any cell in the required output area contains a value, Excel may return #SPILL!. Dynamic arrays also expand or contract as source data changes, which makes them useful for reusable reports.

5.1 FILTER

FILTER returns records meeting a Boolean condition:

=FILTER(tblSales,tblSales[Region]=$B$2,"No matching records")

The include argument evaluates to TRUE or FALSE for each row. Multiple conditions can be combined. In Excel, multiplication commonly represents logical AND:

=FILTER(tblSales,(tblSales[Region]=$B$2)*(tblSales[SalesAmount]>=1000),"No matches")

Each condition must produce a compatible array of row-level results. Logical OR can often be represented with addition, although care is needed to avoid counting or interpreting duplicate TRUE results incorrectly.

To return selected columns rather than the entire table, use CHOOSECOLS where available:

=CHOOSECOLS(FILTER(tblSales,tblSales[Region]=$B$2),1,2,5,8)

5.2 SORT and SORTBY

SORT orders an array:

=SORT(FILTER(tblSales,tblSales[Region]=$B$2),8,-1)

Here, the filtered result is sorted by its eighth column in descending order. SORTBY is often clearer because it specifies a separate sort array:

=SORTBY(FILTER(tblSales,tblSales[Region]=$B$2),FILTER(tblSales[SalesAmount],tblSales[Region]=$B$2),-1)

SORTBY is useful when the displayed columns and sorting variable are different. The sort array must align with the rows being sorted.

5.3 UNIQUE

UNIQUE returns distinct values:

=UNIQUE(tblSales[Region])

A sorted list of distinct regions can be created with:

=SORT(UNIQUE(tblSales[Region]))

UNIQUE can also identify values appearing exactly once by setting its exactly_once argument to TRUE. This distinction is important: a distinct list retains one copy of repeated values, whereas an exactly-once list excludes values that occur more than once.

5.4 SEQUENCE and Generated Structures

SEQUENCE generates ordered numbers:

=SEQUENCE(12,1,1,1)

This produces 12 rows beginning at 1 and increasing by 1. The general conceptual structure is SEQUENCE(rows, columns, start, step). It is useful for period labels, ranking scaffolds, scenario numbers, and controlled test data.

For example, a monthly index can be generated with:

=SEQUENCE(12,,1,1)

SEQUENCE does not create business meaning by itself. The analyst must define whether periods represent calendar months, fiscal months, or sequential observations.

5.5 Combining Dynamic Functions

Dynamic functions become especially powerful when composed:

=SORT(UNIQUE(FILTER(tblSales[CustomerID],tblSales[Region]=$B$2)))

This formula returns a sorted list of unique customers in the selected region. A report may then use the spilled list as input to a lookup or aggregation formula. The spill operator # refers to the entire spilled range. If the formula begins in D5, D5# refers to all cells currently spilled from D5.

For example:

=COUNTIF(tblSales[CustomerID],D5#)

This can return counts for every customer in the dynamic list, subject to Excel version and array behavior.

6. Integrating Datasets

Integration means combining information from different sources while preserving meaning and traceability. A common pattern is a fact table plus dimension or reference tables:

Figure: A transaction table can be enriched using keys that connect it to reference tables.

Before integrating, establish the relationship cardinality. A one-to-many relationship means one product may appear in many sales rows, but each ProductID should appear once in the Products table. If the supposed lookup table contains duplicate keys, a single-result lookup is conceptually ambiguous.

A robust integration process is:

1. Identify the business meaning of each dataset.
2. Confirm the key and its expected uniqueness.
3. Standardize data types and formatting.
4. Test missing and duplicate keys.
5. Join or look up attributes.
6. Reconcile record counts and totals.
7. Document source, date, transformations, and assumptions.

A lookup returning “Not found” is not merely a cosmetic problem. It may indicate a genuine unmatched record, a data-entry error, a changed code, or a type mismatch such as numeric 101 versus text “101”.

7. Worked Example: Regional Sales Analysis

Assume tblSales contains OrderID, ProductID, CustomerID, Region, Quantity, UnitPrice, and SalesAmount. tblProducts contains ProductID, ProductName, and Category.

Step 1: Enrich the transaction table

In a new Category column:

=XLOOKUP([@ProductID],tblProducts[ProductID],tblProducts[Category],"Unmatched product")

This creates an explicit exception label rather than silently returning zero or an error.

Step 2: Produce a region list

=SORT(UNIQUE(tblSales[Region]))

Step 3: Return high-value North-region transactions

If B2 contains North:

=SORTBY(FILTER(tblSales,(tblSales[Region]=$B$2)*(tblSales[SalesAmount]>=1000),"No qualifying sales"),FILTER(tblSales[SalesAmount],(tblSales[Region]=$B$2)*(tblSales[SalesAmount]>=1000),""),-1)

The first FILTER creates the records; the second creates the aligned sorting values. In practice, a helper calculation or LET function may improve readability:

=LET(
 data,FILTER(tblSales,(tblSales[Region]=$B$2)*(tblSales[SalesAmount]>=1000),"No qualifying sales"),
 SORTBY(data,CHOOSECOLS(data,8),-1)
)

The exact column number must correspond to SalesAmount in the returned array. Analysts should verify this rather than assume a fixed position.

Step 4: Summarize selected customers

If D5 contains a spilled unique customer list:

=SUMIFS(tblSales[SalesAmount],tblSales[CustomerID],D5#)

This produces a corresponding array of sales totals. The result can be sorted with a customer label and total array using SORTBY.

The analysis is reusable because the region selector, table rows, and dynamic lists can change without manually copying formulas. However, reusability depends on consistent source structure and adequate space for spilled results.

8. Illustrative Case: Northstar Office Supplies

Background

Northstar Office Supplies receives a monthly sales export from its ordering system. The export contains 18,000 rows and is supplemented by separate product and customer files. Managers want a regional exception report identifying transactions above $1,000 and products belonging to the “Technology” category.

Problem

The existing workbook uses fixed ranges, manually copied VLOOKUP formulas, and pasted monthly lists. When new rows are added, formulas are sometimes omitted. Product codes also appear as numbers in one file and text in another.

Analysis

The analyst first creates three Tables: tblSales, tblProducts, and tblCustomers. ProductID uniqueness is tested in tblProducts using a duplicate check based on COUNTIF. Data types are standardized before lookup formulas are applied. A reconciliation confirms that total SalesAmount in the imported table agrees with the source system.

Approach and Solution

Category is added to tblSales using XLOOKUP with an explicit “Unmatched product” result. A dynamic report uses FILTER to select records meeting all required conditions and SORTBY to rank them by SalesAmount. UNIQUE generates the list of regions and product categories used in validation controls. A separate exception view lists unmatched ProductIDs rather than hiding them.

Outcome

The report updates when the monthly export is replaced, provided the required column names remain stable. Managers can select a region and review qualifying transactions without manually copying formulas. The exception list also reveals 23 product codes requiring master-data correction.

Learning Points

The main improvement did not come from a single formula. It came from combining tidy structure, validated keys, Table-based references, explicit error handling, and dynamic outputs. Automation is dependable only when the data model is dependable.

9. Errors, Limitations, and Controls

Common problems include:

• #N/A: a lookup did not find the key, or the key is formatted inconsistently.
• #SPILL!: a dynamic-array output area is blocked.
• #VALUE!: incompatible data types or invalid arguments.
• duplicate lookup keys: one key maps to multiple possible results.
• hidden filters: users may mistake a filtered view for the complete dataset.
• stale external data: formulas may be correct while source data is outdated.
• silent coercion: numbers stored as text may behave differently in comparisons and aggregations.

Controls should include data validation, duplicate-key tests, unmatched-key reports, reconciliation of totals, protected formula areas, and clear refresh instructions. A workbook should identify its source, last refresh date, owner, assumptions, and known limitations.

Dynamic arrays are not a substitute for a database or an enterprise data pipeline. Large volatile workbooks may become slow, and formulas can be difficult to govern when many users modify them. Power Query, Power Pivot, relational databases, or governed business-intelligence platforms may be preferable for large-scale recurring processes.

10. Ethical and Governance Considerations

Data organization also has ethical implications. A lookup can join customer transactions to demographic or behavioral attributes, potentially creating sensitive profiles. Analysts must use only authorized data, minimize unnecessary personal information, and respect privacy, retention, and access policies.

A professional workbook should preserve traceability: users should be able to identify where data came from, what transformations occurred, and who approved assumptions. Analysts must not hide unmatched records, selectively filter inconvenient observations, or present a dynamic result without communicating its refresh status. Transparency is part of analytical quality.

Artificial intelligence tools may assist with formula suggestions, documentation, or error diagnosis, but generated formulas must be tested against known cases. Confidential data should not be pasted into an external AI service without authorization. Human review remains necessary because a syntactically valid formula may express the wrong business definition.

Best-Practice Checklist

Before delivering a lookup-based workbook, verify that:

• source data is stored in well-designed Tables;
• each key has a documented meaning and expected uniqueness;
• dates and numbers have correct data types;
• exact matching is used for identifiers unless approximate matching is intentional;
• missing results are visible and investigated;
• dynamic-array spill areas are clear;
• formulas are tested with ordinary, boundary, missing, and duplicate cases;
• totals reconcile with the source system;
• reports distinguish filtered views from complete populations;
• the workbook documents its source, owner, refresh date, and assumptions.

Chapter Summary

Reliable Excel analysis begins with tidy data: one observation per row, one variable per column, and one value per cell. Excel Tables provide expandable, named structures that support filtering, calculated columns, and readable structured references. Keys and relationships determine whether datasets can be integrated safely.

XLOOKUP is generally the preferred modern method for one-result lookups, while INDEX–MATCH remains useful for compatibility and flexible range control. FILTER is appropriate when one condition returns many records. SORT, SORTBY, UNIQUE, and SEQUENCE support dynamic, reusable outputs. Dynamic arrays reduce manual copying but require unobstructed spill ranges and careful control of array alignment.

Professional practice requires more than functional formulas. Analysts must validate keys, handle missing values explicitly, reconcile totals, document assumptions, protect sensitive information, and select tools appropriate to the size and repeatability of the task.

Key Terms

Tidy data: Data organized so each row is an observation, each column is a variable, and each cell contains one value.

Excel Table: A named, structured range with filtering, expansion, and structured-reference features.

Primary key: A field or combination of fields that uniquely identifies a record in a table.

Foreign key: A field that refers to a key in another table.

Structured reference: A formula reference using Table and column names rather than ordinary cell coordinates.

Lookup: The process of retrieving a value associated with a key.

XLOOKUP: A modern Excel function for exact or approximate horizontal and vertical lookups.

INDEX–MATCH: A lookup pattern combining position retrieval with value retrieval.

Exact match: A lookup condition requiring the key to equal the search value.

Approximate match: A lookup condition returning the closest value according to defined ordering rules.

Dynamic array: An array result that automatically spills into multiple worksheet cells.

Spill range: The cells populated by a dynamic-array formula.

FILTER: A function that returns records satisfying an inclusion condition.

SORTBY: A function that sorts an array using one or more aligned sort arrays.

UNIQUE: A function that returns distinct or exactly-once values.

SEQUENCE: A function that generates an ordered numeric array.

Cardinality: The number relationship between records in related tables, such as one-to-many.

Reconciliation: Comparing independently calculated totals or counts to confirm consistency.

Data governance: Policies and controls concerning data quality, access, documentation, and responsible use.

Review Questions

5 Short Answer Questions

1. What are the four principal rules of tidy data?
2. What is the difference between a primary key and a foreign key?
3. Why is exact matching normally preferred for identifiers?
4. What causes a #SPILL! error?
5. How does a structured reference differ from a conventional cell reference?

5 Long Answer Questions

1. Explain how Excel Tables improve the reliability and maintainability of recurring business analyses.
2. Compare XLOOKUP, INDEX–MATCH, VLOOKUP, and FILTER in terms of capability and appropriate use.
3. Explain how duplicate keys and missing keys affect dataset integration.
4. Discuss the benefits and limitations of dynamic-array formulas in professional reporting.
5. Design a governance process for a workbook that integrates sales, product, and customer datasets.

3 Analytical / Application Questions

1. A product table contains duplicate ProductIDs, while the sales table contains 10,000 transactions. Explain how you would diagnose the problem and decide whether XLOOKUP is appropriate.
2. Design formulas that return a sorted, unique list of customers whose sales in the selected region exceed a specified threshold. State the assumptions your formulas require.
3. A monthly report shows a lower total than the source system after a new export is loaded. Develop a systematic investigation using Tables, lookups, filters, and reconciliation checks.

Multiple Choice Questions with Answers

Question 1

Which structure best follows the principles of tidy data?

A. One row per observation and one column per variable

B. Several variables combined in each cell

C. Separate subtotals inserted between records

D. One worksheet column for each individual transaction

Correct Answer: A. One row per observation and one column per variable

Question 2

Which structured reference refers to the current row's Quantity value in an Excel Table?

A. tblSales[Quantity]

B. [@Quantity]

C. $Quantity$

D. Quantity!A1

Correct Answer: B. [@Quantity]

Question 3

Which function is most appropriate when one ProductID should return one Category from a reference table?

A. XLOOKUP

B. SEQUENCE

C. SORT

D. UNIQUE

Correct Answer: A. XLOOKUP

Question 4

What is the principal purpose of the 0 argument in MATCH in an INDEX–MATCH formula?

A. To sort the lookup range

B. To request an exact match

C. To return the first row automatically

D. To ignore blank cells

Correct Answer: B. To request an exact match

Question 5

Which function is designed to return multiple records satisfying a condition?

A. FILTER

B. XLOOKUP

C. INDEX

D. MATCH

Correct Answer: A. FILTER

Question 6

What does UNIQUE(tblSales[Region]) normally return?

A. Every region repeated for each transaction

B. Only regions occurring exactly once

C. One copy of each distinct region

D. Regions sorted alphabetically in all Excel versions

Correct Answer: C. One copy of each distinct region

Question 7

If a dynamic-array formula requires cells that already contain values, which error may occur?

A. #DIV/0!

B. #NAME?

C. #SPILL!

D. #NUM!

Correct Answer: C. #SPILL!

Question 8

Which method is generally most suitable for repeatedly importing, cleaning, and combining large datasets?

A. Manual copy and paste

B. Power Query or a governed data pipeline

C. A single VLOOKUP copied into a report

D. Merged cells and color filters

Correct Answer: B. Power Query or a governed data pipeline

Question 9

What is a key risk when a lookup table contains duplicate values for a supposed unique key?

A. The workbook always becomes read-only

B. A single-result lookup may be conceptually ambiguous

C. SORT cannot operate on the table

D. The Table cannot contain calculated columns

Correct Answer: B. A single-result lookup may be conceptually ambiguous

Question 10

What does SEQUENCE(12,1,1,1) generate?

A. One row containing 12 copies of zero

B. Twelve rows beginning at 1 and increasing by 1

C. Twelve columns beginning at 12 and decreasing by 1

D. A random sample of twelve integers

Correct Answer: B. Twelve rows beginning at 1 and increasing by 1