Excel Formulas to Transpose Hierarchical Data by Parent ID

Flattening nested, hierarchical data in Excel is a notorious bottleneck for data analysts. When organizing complex project portfolios backed by standard funding sources like institutional grants or venture capital, aligning Child IDs under their respective Parent IDs manually is highly error-prone.

Fortunately, deploying a dynamic array formula grants users instant, automated visibility into these relational structures. As a key stipulation, this method requires Excel 365 to support dynamic array filtering. For example, mapping Parent ID "101-Admin" to its subsequent sub-tasks becomes entirely hands-off. Below, we explore the exact formula configuration to achieve this seamless transposition.

Hierarchical data is a common way to store structural relationships in databases. Whether you are dealing with an organizational chart (Employees and Managers), a product category tree (Category, Sub-category, Product), or a financial Chart of Accounts, this data is typically structured as an Adjacency List. In this format, each row contains an item (the Child) and a reference to its immediate supervisor or category (the Parent ID).

While this normalized structure is ideal for relational databases, it is highly impractical for analysis, reporting, or pivot tables in Excel. To make this data readable, you need to transpose and flatten the hierarchy so that each level of the relationship occupies its own distinct column (e.g., Level 1, Level 2, Level 3). Doing this dynamically with Excel formulas requires a strategic approach, especially when dealing with variable-depth trees.

In this guide, we will explore how to build robust, dynamic Excel formulas to transpose hierarchical data based on Parent IDs. We will cover modern Office 365 methods (using LET, XLOOKUP, and HSTACK), classic Excel methods for compatibility, and an advanced recursive approach for infinitely deep structures.

The Sample Scenario

To illustrate these techniques, let's establish a sample dataset. Imagine an organizational hierarchy where each employee reports to a manager (identified by their Parent_ID). The ultimate root node (the CEO) has no parent (blank or 0).

Our objective is to transpose this vertical parent-child list into a flattened, multi-column layout like this:

Method 1: The Modern Office 365 Chained Lookup

If you are using Excel 365 or Excel 2021, you have access to dynamic arrays and the powerful LET and XLOOKUP functions. The easiest way to transpose a hierarchy is to perform a bottom-up trace. For any given row, we find its parent, then the parent's parent, and so on, and then arrange them horizontally.

Let's write a formula to construct this path. Suppose your raw data spans $A$2:$C$8. In cell D2, we can trace up to 4 levels of hierarchy using this unified formula:

=LET(
  node, A2,
  nodes, $A$2:$A$8,
  parents, $B$2:$B$8,
  names, $C$2:$C$8,
  
  level_4_name, XLOOKUP(node, nodes, names, ""),
  
  parent_3_id, XLOOKUP(node, nodes, parents, ""),
  level_3_name, XLOOKUP(parent_3_id, nodes, names, ""),
  
  parent_2_id, XLOOKUP(parent_3_id, nodes, parents, ""),
  level_2_name, XLOOKUP(parent_2_id, nodes, names, ""),
  
  parent_1_id, XLOOKUP(parent_2_id, nodes, parents, ""),
  level_1_name, XLOOKUP(parent_1_id, nodes, names, ""),
  
  HSTACK(level_1_name, level_2_name, level_3_name, level_4_name)
)

How this formula works:

• LET variables: We assign names to our primary ranges (nodes, parents, and names) to keep the formula highly readable and performant.
• Chained Lookup: We start with the target node in A2 and look up its name (Level 4). Next, we find its parent's ID (parent_3_id) and use that to look up the parent's name (Level 3). This chain repeats up to the root level.
• HSTACK: Finally, we horizontally stack the resolved names in reverse order, which automatically maps them from left-to-right (Root → Parent → Child → Leaf).

Method 2: Cleaning Up Blanks (Dynamic Shift)

An issue with the previous formula is that if an employee is only at Level 2 (for example, Michael reports directly to Sarah), the higher levels (Level 3 and Level 4) will return empty strings or duplicate values, leaving unsightly gaps on the right or left. To ensure our transposed hierarchy aligns neatly to the left starting from the root, we can filter out empty lookups dynamically.

We can optimize our LET formula by wrapping our horizontal array inside a FILTER function to strip out the empty cells:

=LET(
  node, A2,
  nodes, $A$2:$A$8,
  parents, $B$2:$B$8,
  names, $C$2:$C$8,
  
  l4, XLOOKUP(node, nodes, names, ""),
  p3, XLOOKUP(node, nodes, parents, ""),
  l3, XLOOKUP(p3, nodes, names, ""),
  p2, XLOOKUP(p3, nodes, parents, ""),
  l2, XLOOKUP(p2, nodes, names, ""),
  p1, XLOOKUP(p2, nodes, parents, ""),
  l1, XLOOKUP(p1, nodes, names, ""),
  
  raw_path, HSTACK(l1, l2, l3, l4),
  clean_path, FILTER(raw_path, raw_path <> ""),
  
  clean_path
)

By applying FILTER, any level that doesn't exist for a particular node is compressed out, giving you a clean, left-aligned, contiguous sequence of parent-to-child strings.

Method 3: Classic Excel Method (INDEX & MATCH Helper Columns)

If you or your stakeholders are using older versions of Excel (such as Excel 2013 or 2016) that do not support dynamic array functions or LET, you can achieve the exact same transposed hierarchy using Helper Columns. This classic approach uses INDEX and MATCH to look up parents step-by-step.

Set up helper columns next to your raw table. Let's assume your raw data starts at row 2, and you build your hierarchy starting in Column D:

Step 1: Get the Level 1 Parent (Direct Parent Name)

In column D2, look up the immediate parent's name. Paste this formula and drag it down:

=IFERROR(INDEX($C$2:$C$8, MATCH(B2, $A$2:$A$8, 0)), "")

Step 2: Get the Level 2 Parent (Grandparent Name)

To find the parent of the parent, we must locate the Parent ID of the Parent ID. In column E2, enter this formula:

=IFERROR(INDEX($C$2:$C$8, MATCH(INDEX($B$2:$B$8, MATCH(B2, $A$2:$A$8, 0)), $A$2:$A$8, 0)), "")

While nesting INDEX and MATCH functions can become visually complex, this classic method is incredibly stable and runs fast even in large legacy worksheets.

Method 4: The Ultimate Office 365 Dynamic Solution (Recursive LAMBDA)

For advanced Excel users dealing with hierarchies of unknown or infinite depth, hardcoding chained XLOOKUPs is not scalable. Instead, you can build a custom, recursive function directly in Excel using LAMBDA. This function will trace parents all the way to the root, regardless of whether the path is 3 levels deep or 30 levels deep.

How to set up the Recursive LAMBDA:

1. Go to the Formulas tab on the Excel ribbon and click on Name Manager.
2. Click New to define a new named formula.
3. Name your function: GET_PARENT_PATH
4. In the "Refers to" box, paste the following formula:

=LAMBDA(current_id, nodes, parents, names,
    LET(
        parent_id, XLOOKUP(current_id, nodes, parents, ""),
        current_name, XLOOKUP(current_id, nodes, names, ""),
        IF(
            OR(parent_id = "", parent_id = 0),
            current_name,
            HSTACK(GET_PARENT_PATH(parent_id, nodes, parents, names), current_name)
        )
    )
)

Using your new custom function:

Once saved, you can use this function in your grid just like native Excel functions. Simply enter the following in cell D2 and drag down:

=GET_PARENT_PATH(A2, $A$2:$A$8, $B$2:$B$8, $C$2:$C$8)

This recursive formula evaluates the current ID. If a parent exists, it calls itself (recursively) to find the next parent up the ladder, building a horizontal array on-the-fly. This completely eliminates empty middle nodes, and dynamically scales to handle any organizational depth automatically.

Summary: Choosing the Right Tool

Depending on your version of Excel and your reporting requirements, you can choose the strategy that best fits your workflow:

• Use Method 1 or 2 (LET + HSTACK) if you have Excel 365/2021 and your hierarchy depth is fixed and known (e.g., up to 5 levels).
• Use Method 3 (INDEX + MATCH Helpers) if you need robust backwards compatibility with legacy Excel environments.
• Use Method 4 (Recursive LAMBDA) if you are building enterprise-level spreadsheets and need to support infinite, variable-depth structures without modifying your formulas.