Working with a list of part numbers, we need to discover the part number that would serve as the parent part number for all its child part numbers.
Below is a list of the part numbers and their associated BOM (Bill of Materials) Level.
This is a bit of a tricky operation because we don’t want to discover each part number’s BOM Level value, we already know that rather the BOM Level number of its parent. Once we locate the parent’s BOM Level, we can discover the associated part number.
For example, if we are looking at a part number with a BOM Level of 3, we need to discover the part number at BOM Level 2 associated with that child.
As the part number at BOM Level 0 has no parent, we’ll skip that one for now.
Starting in cell C2, we write the following formula:
=LOOKUP( A3 – 1, $A$2:A3, $B$2:B3 )
We have a couple of interesting and somewhat unconventional tactics happening in this formula.
- The lookup_value is having 1 subtracted from it. This allows us to search for the parent BOM Level value rather than the current BOM Level value as each parent is 1 less than their child.
- The range references for both the lookup_vector and the results_vector have the first portion fixed as an absolute reference (with the dollar signs) while the second portion is left as a relative reference (without the dollar signs).
The idea here is that when the formula is filled down to adjacent rows, the two vectors will expand to examine additional rows of content. This limits the search range to only values up to and including the current position in the list.
It’s the second tactic that is the most interesting, and possibly the most puzzling.
If we examined the entire range of data for both the lookup_vector and results_vector, the results would appear as follows.
Each BOM Level 3 item is discovering “10800-1002” as its parent. This is because the LOOKUP stops when it encounters the first BOM Level 2 entry.
Granted, the first BOM Level 2 entry is in cell A4; we have a series of “2” values in cells A4 through A6. The LOOKUP function rests on the last entry on the duplicated series (cell A6). Thus, the part number in cell B6 (“10800-1002”) is returned for all BOM Level 3 entries.
The references used earlier where the first portion is set as absolute, and the second portion set as relative…
$A$2:A3 and $B$2:B3
… helps somewhat in combating this issue, but not much.
These incorrect results are due to 2 factors:
- Our BOM Level values are not sorted in ascending order.
- The direction that LOOKUP is performing the search.
LOOKUP is searching from the TOP DOWN. We need LOOKUP to search from the BOTTOM UP.
“But wait!”, you say. “Won’t that just cause the opposite problem?”
No, because the expanding range will always limit our search to only the current row and those rows previously examined.
We can’t sort the list by BOM Level, and the LOOKUP function is a bit lacking in configurable options, like searching from the bottom up. We need to get creative with our formula.
To help solve this problem, we will create our custom lookup vector that consists of “True/False”, then ultimately “1s/Errors”.
First, (ignoring the lookup_value for the moment) we modify the lookup_vector argument to compare each entry in the range to the BOM Level minus 1.
For the formula in cell C3, this would resolve each test as shown below; true and false.
PRO TIP: To see the results of each test, highlight the desired argument and press F9. Make certain you press Undo (CTRL-Z) to return the formula to its original state before leaving edit mode.
We need to turn these “true/false” results into “1s/Errors”. This is easily done by dividing each of the results into 1.
Our new test results are below.
This way of testing will only yield 1s and errors.
Looking at the last entry in the table (cell C17), the formula appears like so…
…and resolves each test in the following way.
Here’s Where the Magic Takes Place
Since an array of tests will only result in 1s and errors, we need only search for any value greater than 1, like 2.
LOOKUP will search the entire range for the number 2, never find it, then settle on the last number it saw during its search. This will be the last “1” in the test results.
Gilding the Lily
To have something displayed for any BOM Level 0 entries, we will place the formula in cell C2 which returns a divide by zero error because there is no BOM Level -1. (The test returns a “FALSE” which is interpreted by Excel as a zero).
We will suppress this error and replace it with a dash using the IFERROR function.
=IFERROR(LOOKUP(2, 1/(A2-1=$A$2:A2), $B$2:B2), "-")