# Excel’s LOOKUP function with XLOOKUP Alternative

Functions that perform lookup operations are indispensable to spreadsheet users.

Excel has many functions capable of performing lookups: **VLOOKUP**, **HLOOKUP**, **INDEX/MATCH**, and the newer **XLOOKUP**.

However, the function that started it all was the original **LOOKUP** function.

Although not as flexible as the lookup functions that followed it, the **LOOKUP** function is elegant due to its simplicity. In the right situations, it can even boast of being the easiest to use.

Let’s look at a demonstration of this oft-overlooked function and see how it compares to the newest lookup function in the Excel toolkit, **XLOOKUP**.

A while ago, I made a video/post called “**10 Functions for Engineers**” (*link below*) wherein one of the solutions a formula incorporated the **LOOKUP** function as part of its solution.

This was used to locate the immediate parent node in a hierarchy of nodes.

### 10 Excel Functions You NEED to KNOW as Engineers!

Several viewers commented that they wanted to better understand how the **LOOKUP** function operated in this scenario.

Let’s break it down to its core elements and then see if there is a more modern approach to solving the same problem.

# Looking Up Data the “Old School” Way

The **LOOKUP** function is quite simple in its design. The syntax for **LOOKUP** is as follows:

LOOKUP(lookup_value, lookup_vector, [result_vector] )

**lookup_value**(*required*) – A value that**LOOKUP**searches for in the first vector.**Lookup_value**can be a number, text, a logical value, or a name or reference that refers to a value.**lookup_vector**(*required*) – A range that contains only one row or one column. The values in**lookup_vector**can be text, numbers, or logical values.**result_vector**(*optional*) – A range that contains only one row or column. The**result_vector**argument must be the same size as lookup_vector. It must be the same size.

To put it in plain English:

- What do you want to find?
- Where do you want to find it?
- What do you want to be returned?

Our example below shows a list of employee names and salaries.

We wish to locate the employees’ salaries in a list of salary ranges to return a bonus percentage.

If an employee’s salary is not expressly defined in the Salary/Bonus list, we need to return the bonus where the salary is as close to the customer’s salary without going over (*i.e., exceeding*).

Example: If an employee has a salary of $49,000, the returned bonus would be 5%. Although $49,000 is closer to $50,000 than $30,000, the $50,000 cutoff exceeds $40,000. Thus, $30,000 is the closest salary on the list without going over $40,000.

Starting with Kim West’s entry (*cell C2*), we can write the following formula:

=LOOKUP( B2, $G$2:$G$6, $H$2:$H$6 )

Note: The references to ranges **G2:G6** and **H2:H6** must be referred to absolutely so the references do not change when the formula is filled (*i.e., repeated*) to the adjacent rows below.

This appears to work perfectly.

Did you notice in the **LOOKUP** function there was no decision on whether the function should look for an exact match or an approximate match?

All uses of **LOOKUP** default to an __approximate match__. There is no option for limiting the logic to an __exact match__ posture.

## Important Point About the LOOKUP Function

One of the requirements for **LOOKUP** to operate properly is that the **lookup_vector** (in this case, **$G$2:$G$6**) __must__ be sorted in ascending order.

If the list is stored in any other way, the **LOOKUP** function will almost certainly return either incorrect results or errors.

## Strengths and Weaknesses

Suppose we need to locate the employee ID in the following table by using the employee’s name.

A more traditionally used lookup function like **VLOOKUP** would be unable to perform this operation without the help of other advanced functions like **CHOOSE**. This is because the **results_vector** lies to the __left__ of the **lookup_vector**. This is a major league no-no with **VLOOKUP**.

On the other hand, because these vectors are defined explicitly by the **LOOKUP** function, a specific arrangement is not required.

Starting with Kim West’s entry (*cell D2*), we can write the following formula:

=LOOKUP( A2, $M$2:$M$11, $K$2:$K$11 )

Regrettably, this did not produce the desired results.

I’m sure you’ve already guessed that the reason for this inaccuracy is due to the sort order of the lookup data. The lookup table is sorted in __ascending order__ by **Employee ID**, not by **Employee (Name)**.

If we sort the lookup table in __ascending order__ by **Employee** we are delivered the expected results.

Remember, when using the **LOOKUP** function your lookup vector needs to be in ascending order.

# Discovering Parent Nodes in a Hierarchical List

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), "-")

# Using a More Modern Approach to Solving the Problem

Lookup functions have evolved, providing new functionality and erasing limitations. No function has done more to advance these directions than the **XLOOKUP** function.

**XLOOKUP** uses the same “dual-vector” approach to selecting lookup and search vectors. It also can search from top-to-bottom or bottom-to-top as well as possesses built-in error control.

If we use the **XLOOKUP** function to solve the previous problem, the formula will look like the following:

Compared to the old-school **LOOKUP** with its confusing division process, **XLOOKUP**’s more elegant approach yields the same results.

## Understanding the XLOOKUP Example

The **XLOOKUP**’s arguments (*starting in cell D2*) are as follows:

**A2-1**– This serves the same purpose: reduce the current BOM Level by 1.**$A$2:A2**– The dynamically resizing**lookup_vector**.**$B$2:B2**– The dynamically resizing**results_vector**.**“-”**– The text to display when no result is discovered.**0**– The**match_mode**. In this case,**0**equates to “**exact match**” mode.**-1**– The**search_mode**. In this case,**-1**equates to “search last-to-first” (*e., bottom-up*).

It’s that final argument that makes it work as desired.

## Practice Workbook

Feel free to Download the Workbook HERE.