Download the practice workbook 👉 HERE and follow along.
The LOOKUP function in Excel is a powerful tool for finding data in a table. While there are newer methods like VLOOKUP and XLOOKUP, the LOOKUP function is still useful for certain tasks, especially when you need an approximate match.
How to use the LOOKUP Function in Excel
The LOOKUP function helps you find a value in one column (or row) and return a value from another. For example, you can look up an employee’s salary and return their bonus percentage from another list.
Here’s the basic formula to search Excel with the LOOKUP formula:
LOOKUP(lookup_value, lookup_vector, [result_vector] )- lookup_value (required) – This is what you’re searching for (a number, text, or reference).
- lookup_vector (required) – This is the range where Excel will search for your value (a column or row).
- result_vector (optional) – This is the range from which Excel will return a matching value. It must be the same size as the lookup_vector.
Basic Example – Excel LOOKUP Function
Let’s say you have a list of employee salaries and you want to find their bonus percentage from a separate table.
The table shows bonus percentages based on salary ranges.
Here’s how to do it:
Step 1: Understanding Approximate Match
The LOOKUP function is designed to return an approximate match by default. But what does this mean?
Imagine you have a list of salary ranges, and you want to find the bonus percentage for each employee based on their salary. If an employee’s exact salary isn’t in the list, the LOOKUP function will return the closest lower value.
In other words, the function will find the largest number in your list that doesn’t exceed the employee’s salary.
For example:
Let’s say an employee earns $49,000 and your salary cutoffs are $30,000, $40,000, and $50,000. Then the LOOKUP function will return the bonus for the $40,000 range, because $50,000 is too high.
Take a moment to think: how might this be helpful in your work? It’s useful for tax brackets, discounts, or bonus percentages like in our example!
Step 2: Sorting Your Data
Before using the LOOKUP function, there’s something very important you need to know: your data must be sorted in ascending order. If it isn’t sorted from smallest to largest, the function may give incorrect results or return errors.
To check this:
- Select the range of data you want to use.
- On the toolbar, click Data > Sort.
- Choose Sort by and select the column you want sorted, making sure it’s in ascending order (from smallest to largest).
Step 3: Enter the LOOKUP Formula
Now that you understand approximate matches and have your data sorted, you’re ready to write the formula.
=LOOKUP(B2, $G$2:$G$6, $H$2:$H$6)- B2 contains the salary of the employee.
- $G$2:$G$6 is the range of salary thresholds.
- $H$2:$H$6 contains the corresponding bonus percentages.
💡 Note: The dollar signs in the formula ($G$2:$G$6 and $H$2:$H$6) make these references absolute. This means that when you copy the formula to other rows, Excel won’t change these ranges. The formula will always refer to the exact same cells, ensuring the correct data is pulled for each employee.
Want to learn more about absolute and relative cell references? Check out our complete guide to Excel cell references.
Step 4: Check Your Results
After entering the formula, check the results to ensure that the function is working as expected.
If everything is sorted correctly, Excel will return the closest match from the salary list and display the correct bonus percentage.
Featured Course
Master Excel’s Essential Modern Functions
LOOKUP vs VLOOKUP Function
Now that you understand how LOOKUP works, you might wonder how it compares to the popular VLOOKUP function.
Let’s break down the key differences so you can decide which one to use based on your needs.
Left Lookup
- One of LOOKUP’s strengths is that it doesn’t require the lookup value to be in the first column. You can search in any column and return data from any other, giving you more flexibility in how your data is structured.
- With VLOOKUP, the lookup value must always be in the first column of your data range. If your data isn’t structured this way, you might need to rearrange it or use additional functions to get the correct results.
Exact vs. Approximate Match
- LOOKUP always performs an approximate match by default. There is no option to search for an exact match, which can be a limitation if you need precision.
- VLOOKUP gives you the choice to search for either an exact match or an approximate match. You can specify this by adding TRUE for approximate match or FALSE for exact match at the end of the formula.
Sorting Requirements
- Your data must be sorted in ascending order when using LOOKUP. If it’s not sorted, the results may be inaccurate or return errors.
- Sorting is only required for VLOOKUP when using approximate match (TRUE). If you’re using exact match (FALSE), sorting is not necessary.
Example: Discovering Parent Nodes in a Hierarchical List
When working with hierarchical data, such as a Bill of Materials (BOM), it’s important to identify the parent part number of each child part number.
We’ll show you how to use the LOOKUP function to find a parent node based on its BOM Level.
Understanding the Problem
Here’s what we’re trying to do: Given a part number with a specific BOM Level, we want to find its parent part number, which is at the level immediately above.
For example, if a part is at BOM Level 3, we want to find the part at BOM Level 2 that acts as its parent.
Parts at BOM Level 0 have no parent, so we’ll ignore them.
Key Concepts Before Writing the Formula
Before we dive into the formula, there are two key points to understand:
Approximate Match and Parent Levels
When using LOOKUP, we need to search for the BOM Level one level above the child.
For example, if we are looking at a part in BOM Level 3, we search for BOM Level 2 as its parent.
Relative vs. Absolute Cell References
The formula uses a mix of absolute references (which stay fixed as the formula is copied) and relative references (which change as you move down the rows). This allows us to expand the search range dynamically while keeping the base of the range constant.
Step-by-Step Formula Breakdown
Let’s start in cell C2 and write the formula to find the parent part number for a part number at BOM Level 3.
Here’s the formula:
=LOOKUP(A3-1, $A$2:A3, $B$2:B3)- A3-1: This is the lookup_value, where we subtract 1 to search for the parent BOM Level (e.g., searching for BOM Level 2 if we’re working with BOM Level 3).
- $A$2:A3: This is the lookup_vector—the range that contains the BOM Level values. Notice that $A$2 is an absolute reference (it won’t change), while A3 is relative (it will change as we move down the rows). This allows the range to expand as we fill the formula down.
- $B$2:B3: This is the result_vector—the range that contains the corresponding part numbers. Again, $B$2 is absolute, while B3 is relative, so the range expands as we move down the list.
Why Use Mixed References?
Using a mixed reference (with dollar signs) in the ranges ensures that when the formula is copied to other rows, the starting point remains fixed at row 2, but the end point of the range adjusts as the formula moves downward.
This limits the search to the relevant rows and prevents Excel from searching the entire table.
Problem with Top-Down Search and Duplicates
However, there’s a challenge with this approach. The LOOKUP function searches top-down, meaning it stops at the first matching BOM Level it finds.
If you have multiple entries with the same BOM Level, it may not return the correct parent.
For example, if you have several parts at BOM Level 2, LOOKUP may return the last part in that list, even if it isn’t the true parent.
To solve this problem, we need to flip the search direction so that LOOKUP starts from the bottom and works upwards. Unfortunately, LOOKUP doesn’t support bottom-up searching, so we need a creative workaround.
Step-by-Step: Creating a Bottom-Up Search
To solve this, we’ll create a custom lookup_vector that uses True/False logic, which Excel will interpret as 1s and Errors.
1. Create the Test Condition
First, we modify the lookup_vector to compare each entry in the range to the current BOM Level minus 1.
This checks whether each BOM Level matches the parent level.
Example:
A3-1 = $A$2:A3
This creates an array of True/False results.
💡 Pro Tip: To see the results of each test, highlight the desired argument and press F9. Make sure to press Undo (Ctrl + Z) to return the formula to its original state before leaving edit mode.
2. Convert True/False to 1s and Errors:
To convert these True/False values into numbers that Excel can process, divide by 1.
This turns True into 1 and False into an Error.
Example:
1/(A3-1=$A$2:A3)
3. Final LOOKUP Formula
Now that we have an array of 1s and errors, we use LOOKUP to find the last 1 in the array, which corresponds to the parent BOM Level.
Here’s the complete formula:
=IFERROR(LOOKUP(2, 1/(A2-1=$A$2:A2), $B$2:B2), "-")- The LOOKUP function searches for the value 2, which doesn’t exist, so it stops at the last 1 it finds, giving us the correct parent.
- The IFERROR function catches any errors, such as when we’re working with BOM Level 0 (which has no parent), and replaces them with a dash (“-“).
Featured Course
Master Excel’s Essential Modern Functions
LOOKUP vs XLOOKUP Function
Excel has come a long way, and today we have more powerful functions that simplify complex tasks.
One of the most advanced tools for finding data is the XLOOKUP Excel function. It solves many of the limitations of older lookup functions like LOOKUP and VLOOKUP.
With XLOOKUP in Excel, you get:
- Flexible search modes: You can search from top to bottom or bottom to top, depending on your needs.
- Built-in error handling: No need to add additional formulas to handle errors like missing values.
- Exact and approximate match options: You can choose between exact matches or find the closest result, making it more versatile.
Now, let’s see how XLOOKUP works for the same problem.
Understanding the XLOOKUP Excel Formula
Here’s how the XLOOKUP function would look to solve the problem of finding the parent part number for each child part:
=XLOOKUP(A2-1, $A$2:A2, $B$2:B2, "-", 0, -1)Let’s break down each part of the formula:
- A2-1: This is the lookup_value—just like before, we reduce the BOM Level by 1 to search for the parent level.
- $A$2:A2: This is the lookup_vector (the column containing BOM Levels). The reference is dynamic: as we copy the formula down, it will automatically expand the range to include all relevant rows up to the current one.
- $B$2:B2: This is the results_vector (the column containing part numbers). Like the lookup vector, it also dynamically resizes as we move down the rows.
- “-“: This is the value that will display if no parent is found (for example, if a part is at BOM Level 0 and has no parent).
- 0 (match_mode): This specifies an exact match—we only want to find an exact BOM Level match for the parent.
- -1 (search_mode): This is the key to solving our problem. It tells XLOOKUP to search from bottom to top (last-to-first), ensuring we find the most recent parent in the list.
This formula will search the BOM Levels from the bottom up, matching the correct parent for each child part number. If no parent is found (for BOM Level 0), the formula will return a “-” instead of an error.
Want to dive deeper into XLOOKUP’s features and how it can make your data lookups faster and easier? Check out our in-depth guide to Excel’s best lookup function: XLOOKUP and learn how to master it for all your spreadsheet tasks!
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the LOOKUP function in Excel with hands-on examples.
Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
