Discovering Data Using the LOOKUP Function
There will be many occasions where you need to discover an unknown piece of information based on a known piece of information.
An example may be something as simple as “How much does this chocolate chip cookie cost?”, or something more involved like “How many of this particular part number do we have in stock?”
Simple or involved, these questions can be solved using a lookup function.
Lookup functions have been part of Excel since its inception. Many methods have been developed over the years to discover related information. Functions like:
Each of these functions has its strengths and weaknesses. Choosing the right one depends on the task at hand coupled with your knowledge and comfort level with each function.
The simplest (and oldest) lookup function is called LOOKUP. LOOKUP has lost favor over the years as more sophisticated, more capable functions have arrived on the scene. But that doesn’t mean it has lost its value.
LOOKUP lacks the pretentiousness of other functions, like INDEX/MATCH. Its charm is in its simplicity.
LOOKUP has the following syntax:
LOOKUP( lookup_value, lookup_vector, [result_vector] )
In simpler terms:
- lookup_value – is the thing you know, like a part number, employee number, or cost code.
- lookup_vector – is the set of cells (a single row or column of cells) where the lookup_value may reside.
- [result_vector] – is the set of cells (a single row or column of cells) where the related item may reside.
For more detailed information and example on the use of LOOKUP, see the Microsoft Documentation for Excel’s LOOKUP function.
For our example, we want to discover the part number for the immediate parent of any child-level part number.
- Level 1’s parent is Level 0
- Level 2’s parent is Level 1
- Level 3’s parent is Level 2
To discover this, we can take the BOM Level of a child item (ex: Part Number “10410-1001” on row 7 has a BOM Level of “1”) and locate the nearest level “0” BOM Level that is above the current row.
Using the example above, the nearest BOM Level “0” is on row 2, Part Number “10420-1001”. (See the purple entries in the previous image.)
We can’t just write a lookup function that finds the first BOM Level that is 1 less than the current BOM Level.
At BOM Level “2”, there can be many level “1” entries above the current row. We must locate the closest (i.e., last listed) parent-level value that is above our current position.
We would need to ignore the level “1” item on row 3 and discover the level “1” item on row 7.
The formula we will use here is more sophisticated than most users are accustomed to. Perhaps a future post will break down the logic with greater specificity.
For now, we’ll just type it in as it relates to a lookup range in a LOOKUP function without diving into the details.
Starting in cell C2 of our example, we’ll write the following formula:
=LOOKUP(2, 1 / ($A$2:A2 = A2 - 1), $B$2:B2)
The components are:
- lookup_value – 2
- lookup_vector – 1 / ($A$2:A2 = A2 – 1)
- [result_vector] – $B$2:B2
Hitting ENTER and filling the formula down the column produces the following results.
Observe the #DIV/0! error on row 2.
This is because BOM Level “0” has no parent-level item.
We can suppress this error message using the IFERROR function.
Displaying Custom Error Messages Using the IFERROR Function
Because many of the default Excel error messages are less than helpful to most users, we can trap the ugly error message and replace it with most anything we wish.
This is accomplished using the IFERROR function. IFERROR allows us to test a formula’s result. If the result is one of Excel’s built-in error messages, we can substitute the error with one of the following:
- Nothing (e., empty text)
- A text response
- A static value
- A different action via a formula
Since we know our BOM Level “0” items will return errors, we will trap the original errors and replace them with dashes.
=IFERROR(LOOKUP(2, 1 / ($A$2:A2 = A2 - 1), $B$2:B2), "-")
Now we have a more pleasing dash instead of an ugly error message.