Solved How to create a recursive LAMBDA function to calculate the total path depth of a nested bill of materials

📌 The Problem Explained

In manufacturing and supply chain management, a Bill of Materials (BOM) often contains multiple layers of sub-assemblies. A single “Parent” part might contain several “Child” parts, which in turn are parents to even smaller components.

Standard Excel functions like VLOOKUP or INDEX/MATCH are designed for flat data and cannot easily “climb” a hierarchy to tell you how many levels deep a specific part is buried. Without recursion, users are often forced to create complex helper columns for every possible level (Level 1, Level 2, etc.), which is inefficient and prone to errors when the product structure changes.

💡 The Quick Solution

To solve this, we create a named recursive function called GET_DEPTH. Open the Name Manager (Ctrl + F3) and define the following formula:

=LAMBDA(current_child, child_range, parent_range,
    LET(
        parent_id, XLOOKUP(current_child, child_range, parent_range, ""),
        IF(parent_id = "", 0, 1 + GET_DEPTH(parent_id, child_range, parent_range))
    )
)

Once defined, you can use it in your worksheet like this: =GET_DEPTH(A2, $A$2:$A$100, $B$2:$B$100)

🛠️ Step-by-Step Breakdown

Follow these steps to implement the function in your workbook:

1. Navigate to the Formulas tab on the Ribbon.
2. Click on Name Manager and then select New.
3. In the Name field, type GET_DEPTH.
4. In the Refers to box, paste the logic explained below:

Argument	Purpose	Example / Logic
current_child	The specific part ID you are currently checking.	A2
child_range	The full column containing all Child part IDs.	$A$2:$A$100
parent_range	The full column containing the corresponding Parent IDs.	$B$2:$B$100
XLOOKUP	Finds the immediate parent of the current child.	Returns “Top Level” if empty.
IF Logic	The “Base Case” that stops the recursion if no parent exists.	IF parent = “” then 0.
Recursion	The function calls itself, adding 1 to the count for every level found.	1 + GET_DEPTH(…)

5. Click OK and close the Name Manager.
6. Apply the formula in an empty column next to your BOM data to see the depth levels (0 for top-level parts, 1 for sub-assemblies, etc.).

⚠️ Common Mistakes to Avoid

• Circular References: Ensure that a part is not listed as its own parent or grandparent. If Part A is the parent of Part B, and Part B is the parent of Part A, the LAMBDA function will loop infinitely and return a #NUM! error.
• Absolute vs. Relative References: When applying the function in the worksheet cells, ensure the child_range and parent_range are locked using $ signs (e.g., $A$2:$A$500). If they are relative, the range will shift as you drag the formula down, leading to incorrect “0” results for deep-level parts.