# LOOKUP Function in Excel

Part 1. Introduce

📋 Definition

The LOOKUP function in Microsoft Excel is a powerful and versatile lookup and reference function that allows you to find a value in a specified range (lookup_array) and return a value from another corresponding field (result_array). It helps you search for a value and retrieve a related value from a different location within a table or range of data.

🎯 Purpose

The main purpose of the LOOKUP function is to perform approximate or exact matching of a lookup value within a data range and retrieve a corresponding value. It is beneficial for data analysis, retrieval, and calculations based on specific lookup criteria.

📜 Syntax & Arguments:

The syntax of the LOOKUP function is as follows:

syntax
```LOOKUP(lookup_value, lookup_array, [result_array]) ```

Arguments:

• `lookup_value`: The value that you want to search for within the lookup_array.
• `lookup_array`: The range of cells that contains the values to be searched (it should be in ascending order for the approximate match).
• `result_array`: The cells’ content with the values to be returned based on the lookup result. This argument is optional. If not provided, the function returns the closest match in the lookup_array.

🔙 Return Value:

The LOOKUP function returns the value corresponding to the matched lookup_value from the result_array.

💡 Remarks:

• If `lookup_value` is smaller than the smallest value in the `lookup_array`, the LOOKUP function will return the #N/A error.
• If `lookup_value` is larger than the most significant value in the `lookup_array`, the LOOKUP function will return the last value in the `result_array`.
• The `lookup_array` must be in ascending order for approximate matching to work correctly.

Part 2. Examples

Let’s illustrate the LOOKUP function with three simple examples using Microsoft Excel spreadsheets:

ABCDE
2A9010085=LOOKUP(D2, \$B\$2:\$B\$6, \$A\$2:\$A\$6)
3B808992=LOOKUP(D3, \$B\$2:\$B\$6, \$A\$2:\$A\$6)
4C707978=LOOKUP(D4, \$B\$2:\$B\$6, \$A\$2:\$A\$6)

Explanation: In this example, we have a table with grade letters in Column A, their minimum scores in Column B, and maximum scores in Column C. We want to determine the grade based on the score provided in Column D. The LOOKUP function is used to find the rate corresponding to the given score from B2:B6 (minimum scores) and returns the grade from A2:A6 (grade letters). For instance, the formula in E2 looks up the score (85) in the range D2 (85) and returns the corresponding grade “A.”

Result Table:

ABCDE
2A9010085A
3B808992A
4C707978B

Example 2: Price Tier Lookup

ABCDE
1TierMinMaxPricePrice Tier
2Bronze0500250=LOOKUP(D2, \$B\$2:\$B\$4, \$A\$2:\$A\$4)
3Silver5011000750=LOOKUP(D3, \$B\$2:\$B\$4, \$A\$2:\$A\$4)
4Gold100115001250=LOOKUP(D4, \$B\$2:\$B\$4, \$A\$2:\$A\$4)

Explanation: In this example, we have a table with price tiers in Column A, and their minimum and maximum price ranges in Columns B and C, respectively. We want to determine the price tier based on the price provided in Column D. The LOOKUP function is used to find the price tier that corresponds to the given price from the range B2:B4 (minimum prices) and returns the price tier from A2:A4 (price tiers). For instance, the formula in E2 looks up the price (250) in the range D2 (250) and returns the corresponding price tier, “Bronze.”

Result Table:

ABCDE
1TierMinMaxPricePrice Tier
2Bronze0500250Bronze
3Silver5011000750Silver
4Gold100115001250Gold

Example 3: Product Availability Lookup

ABCDE
1ProductQtyStatusOrder QtyAvailability
2Laptop50In Stock10=LOOKUP(D2, \$B\$2:\$B\$4, \$C\$2:\$C\$4)
3Phone20Low Stock15=LOOKUP(D3, \$B\$2:\$B\$4, \$C\$2:\$C\$4)
4Tablet5Out of Stock8=LOOKUP(D4, \$B\$2:\$B\$4, \$C\$2:\$C\$4)

Explanation: In this example, we have a table with product names in Column A, their available quantities in Column B, and the availability status in Column C. We want to determine the availability status based on the order quantity provided in Column D. The LOOKUP function is used to find the availability status that corresponds to the given order quantity from the range B2:B4 (available quantities) and returns the status from C2:C4 (availability status). For instance, the formula in E2 looks up the order quantity (10) in the range D2 (10) and returns the corresponding availability status “In Stock.”

Result Table:

ABCDE
1ProductQtyStatusOrder QtyAvailability
2Laptop50In Stock10In Stock
3Phone20Low Stock15In Stock
4Tablet5Out of Stock8Low Stock

Example 4: Employee Bonus Calculation

ABCDE
1NameSalesTargetBonusBonus Category
2John\$2000\$1500=(IF(B2 > C2, B2 * 0.05, 0))=LOOKUP(D2, \$G\$2:\$H\$4)
3Sarah\$1800\$2000=(IF(B3 > C3, B3 * 0.05, 0))=LOOKUP(D3, \$G\$2:\$H\$4)
4Mike\$2500\$3000=(IF(B4 > C4, B4 * 0.05, 0))=LOOKUP(D4, \$G\$2:\$H\$4)

Explanation: In this example, we have a table with employee names in Column A, their sales performance in Column B, and their sales target in Column C. We want to calculate the bonus for each employee based on their sales performance compared to the target. If the sales are above the target, the employee receives compensation of 5% of their sales; otherwise, they receive no bonus. The IF function is used to check if the sales performance in Column B is above the target in Column C. The LOOKUP function then categorizes the bonus amount into different categories from the reference table in columns G and H. For instance, the formula in D2 checks if the sales (\$2000) in B2 are above the target (\$1500) in C2. As it is, the bonus is calculated as \$2000 * 0.05 = \$100. The LOOKUP function then looks up this bonus amount (\$100) in E2 from the reference table \$G\$2:\$H\$4 and returns the bonus category “Above Target.”

Result Table:

ABCDE
1NameSalesTargetBonusBonus Category
2John\$2000\$1500\$100Above Target
3Sarah\$1800\$2000\$0Below Target
4Mike\$2500\$3000\$125Above Target

ABCDE
2John8590=AVERAGE(B2:C2)=LOOKUP(D2, \$G\$2:\$H\$6)
3Sarah7885=AVERAGE(B3:C3)=LOOKUP(D3, \$G\$2:\$H\$6)
4Mike9288=AVERAGE(B4:C4)=LOOKUP(D4, \$G\$2:\$H\$6)

Explanation: In this example, we have a table with student names in Column A and their Math (Column B) and Science (Column C) scores. We want to calculate the average score for each student and assign a grade based on the average score using a reference table in columns G and H. The AVERAGE function is used to find the average score for each student. The LOOKUP function then categorizes the average score into different grade levels from the reference table. For instance, the formula in D2 calculates the average score for John as (85 + 90) / 2 = 87.5. The LOOKUP function then looks up this average score (87.5) in E2 from the reference table \$G\$2:\$H\$6 and returns the corresponding grade “A.”

Result Table:

ABCDE
2John859087.5A
3Sarah788581.5B
4Mike928890A

Example 6: Discounted Price Calculation

ABCDE
1ItemPriceDiscountDiscounted PriceDiscount Level
2Apple\$2.510%=B2 * (1 – C2)=LOOKUP(C2, \$G\$2:\$H\$4)
3Banana\$1.85%=B3 * (1 – C3)=LOOKUP(C3, \$G\$2:\$H\$4)
4Orange\$1.215%=B4 * (1 – C4)=LOOKUP(C4, \$G\$2:\$H\$4)

Explanation: In this example, we have a table with item names in Column A, their original prices in Column B, and the discount percentage in Column C. We want to calculate the discounted price for each item based on the discount percentage. The calculation is done by subtracting the discount percentage from 1 and multiplying it by the original price. The LOOKUP function then categorizes the discount percentage into different discount levels from the reference table in columns G and H. For instance, the formula in D2 calculates the discounted price for Apple as \$2.5 * (1 – 0.1) = \$2.25. The LOOKUP function then looks up this discount percentage (10%) in E2 from the reference table \$G\$2:\$H\$4 and returns the discount level “10% Discount.”

Result Table:

ABCDE
1ItemPriceDiscountDiscounted PriceDiscount Level
2Apple\$2.510%\$2.2510% Discount
3Banana\$1.85%\$1.715% Discount
4Orange\$1.215%\$1.0215% Discount

Example 7: Inventory Management

ABCDE
1ProductStockThresholdStatusInventory Status
2Apple10050=(IF(B2 > C2, “In Stock”, “Low Stock”))=LOOKUP(D2, \$G\$2:\$H\$3)
3Banana4050=(IF(B3 > C3, “In Stock”, “Low Stock”))=LOOKUP(D3, \$G\$2:\$H\$3)
4Orange6050=(IF(B4 > C4, “In Stock”, “Low Stock”))=LOOKUP(D4, \$G\$2:\$H\$3)

Explanation: In this example, we have a table with product names in Column A, their current stock levels in Column B, and the threshold stock level in Column C. We want to determine the status of each product’s inventory based on whether the stock level is above or below the threshold. The IF function checks if the stock level in Column B is above the threshold in Column C. If it is, the status is “In Stock”; otherwise, it is “Low Stock.” The LOOKUP function is then used to categorize the inventory status into different levels from the reference table in columns G and H. For instance, the formula in D2 checks if the stock level (100) in B2 is above the threshold (50) in C2. As it is, the status is “In Stock.” The LOOKUP function then looks up this status (“In Stock”) in E2 from the reference table \$G\$2:\$H\$3 and returns the inventory status “Sufficient Stock.”

Result Table:

ABCDE
1ProductStockThresholdStatusInventory Status
2Apple10050In StockSufficient Stock
3Banana4050Low StockLow Stock
4Orange6050In StockSufficient Stock

Example 8: Expense Analysis

ABCDE
1MonthJanFebTotal ExpenseExpense Trend
2Jan\$300\$350=SUM(B2:C2)=LOOKUP(D2, \$G\$2:\$H\$4)
3Feb\$400\$420=SUM(B3:C3)=LOOKUP(D3, \$G\$2:\$H\$4)
4Mar\$320\$300=SUM(B4:C4)=LOOKUP(D4, \$G\$2:\$H\$4)

Explanation: In this example, we have a table with months in Column A, and the corresponding expenses for each month in Columns B and C. We want to calculate the total cost for each month using the SUM function. The LOOKUP function then categorizes the expense trend into different levels from the reference table in columns G and H. For instance, the formula in D2 calculates the total expense for January as \$300 + \$350 = \$650. The LOOKUP function then looks up this total expense (\$650) in E2 from the reference table \$G\$2:\$H\$4 and returns the expense trend “High Expense.”

Result Table:

ABCDE
1MonthJanFebTotal ExpenseExpense Trend
2Jan\$300\$350\$650High Expense
3Feb\$400\$420\$820High Expense
4Mar\$320\$300\$620Medium Expense

Example 9: Shipping Fees

ABCDE
1ItemWeightRegionShipping FeeShipping Class
2Laptop2 kgDomestic=IF(C2=”Domestic”, VLOOKUP(B2, \$G\$2:\$H\$4, 2, TRUE), VLOOKUP(B2, \$G\$5:\$H\$7, 2, TRUE))=LOOKUP(D2, \$K\$2:\$L\$4)
3Book0.5 kgInternational=IF(C3=”Domestic”, VLOOKUP(B3, \$G\$2:\$H\$4, 2, TRUE), VLOOKUP(B3, \$G\$5:\$H\$7, 2, TRUE))=LOOKUP(D3, \$K\$2:\$L\$4)
4Phone0.3 kgInternational=IF(C4=”Domestic”, VLOOKUP(B4, \$G\$2:\$H\$4, 2, TRUE), VLOOKUP(B4, \$G\$5:\$H\$7, 2, TRUE))=LOOKUP(D4, \$K\$2:\$L\$4)

Explanation: In this example, we have a table with item names in Column A, their weights in Column B, and the shipping regions in Column C (either “Domestic” or “International”). We want to calculate the shipping fee for each item based on its weight and shipping region using the VLOOKUP function. If the region is “Domestic,” the shipping fee is looked up from columns G and H in the reference table. Otherwise, if the region is “International,” the shipping fee is looked up from the reference table in columns G and H. The LOOKUP function then categorizes the shipping fee into different shipping classes from the reference table in columns K and L. For instance, the formula in D2 checks if the region (“Domestic”) is in C2. As it is, the shipping fee for the laptop (2 kg) is looked up in VLOOKUP from the reference table \$G\$2:\$H\$4 and returns the shipping fee of \$20. The LOOKUP function then looks up this shipping fee (\$20) in E2 from the reference table \$K\$2:\$L\$4 and returns the shipping class “Standard Domestic.”

Result Table:

ABCDE
1ItemWeightRegionShipping FeeShipping Class
2Laptop2 kgDomestic\$20Standard Domestic
3Book0.5 kgInternational\$10Economy International
4Phone0.3 kgInternational\$10Economy International

Example 10: Sales Commission

ABCDE
1NameSalesTargetCommissionCommission Level
2John\$1000\$800=(IF(B2 > C2, B2 * 0.1, 0))=LOOKUP(D2, \$G\$2:\$H\$4)
3Sarah\$1500\$1200=(IF(B3 > C3, B3 * 0.1, 0))=LOOKUP(D3, \$G\$2:\$H\$4)
4Mike\$800\$1000=(IF(B4 > C4, B4 * 0.1, 0))=LOOKUP(D4, \$G\$2:\$H\$4)

Explanation: In this example, we have a table with employee names in Column A, their sales in Column B, and their sales target in Column C. We want to calculate the commission for each employee based on their sales performance compared to the target. If the sales are above the target, the employee receives a commission of 10% of their sales; otherwise, they receive no commission. The IF function is used to check if the sales performance in Column B is above the target in Column C. The LOOKUP function then categorizes the commission amount into different commission levels from the reference table in columns G and H. For instance, the formula in D2 checks if the sales (\$1000) in B2 are above the target (\$800) in C2. As it is, the commission is calculated as \$1000 * 0.1 = \$100. The LOOKUP function then looks up this commission amount (\$100) in E2 from the reference table \$G\$2:\$H\$4 and returns the commission level “10% Commission.”

Result Table:

ABCDE
1NameSalesTargetCommissionCommission Level
2John\$1000\$800\$10010% Commission
3Sarah\$1500\$1200\$15010% Commission
4Mike\$800\$1000\$0No Commission

Example 11: Test Scores

ABCDE
1NameMathScienceTotal ScoreScore Level
2John8085=SUM(B2:C2)=LOOKUP(D2, \$G\$2:\$H\$4)
3Sarah9078=SUM(B3:C3)=LOOKUP(D3, \$G\$2:\$H\$4)
4Mike7092=SUM(B4:C4)=LOOKUP(D4, \$G\$2:\$H\$4)

Explanation: In this example, we have a table with student names in Column A and their Math (Column B) and Science (Column C) scores. We want to calculate the total score for each student using the SUM function. The LOOKUP function then categorizes the entire score into different score levels from the reference table in columns G and H. For instance, the formula in D2 calculates the total score for John as 80 + 85 = 165. The LOOKUP function then looks up this total score (165) in E2 from the reference table \$G\$2:\$H\$4 and returns the corresponding score level “Above Average.”

Result Table:

ABCDE
1NameMathScienceTotal ScoreScore Level
2John8085165Above Average
3Sarah9078168Above Average
4Mike7092162Above Average

Example 12: Inventory Reorder Level

ABCDE
1ProductStockReorder LevelStatusReorder Status
2Apple10050=(IF(B2 <= C2, “Order”, “No Order”))=LOOKUP(D2, \$G\$2:\$H\$3)
3Banana4050=(IF(B3 <= C3, “Order”, “No Order”))=LOOKUP(D3, \$G\$2:\$H\$3)
4Orange6050=(IF(B4 <= C4, “Order”, “No Order”))=LOOKUP(D4, \$G\$2:\$H\$3)

Explanation: In this example, we have a table with product names in Column A, their current stock levels in Column B, and the reorder level in Column C. We want to determine the reorder status for each product’s inventory based on whether the stock level is below or above the reorder level. The IF function checks if the stock level in Column B is below or equal to the reorder level in Column C. If it is, the status is “Order”; otherwise, it is “No Order.” The LOOKUP function is then used to categorize the reorder status into different levels from the reference table in columns G and H. For instance, the formula in D2 checks if the stock level (100) in B2 is below or equal to the reorder level (50) in C2. As it is, the status is “Order.” The LOOKUP function then looks up this status (“Order”) in E2 from the reference table \$G\$2:\$H\$3 and returns the reorder status “Reorder Required.”

Result Table:

ABCDE
1ProductStockReorder LevelStatusReorder Status
2Apple10050OrderReorder Required
3Banana4050OrderReorder Required
4Orange6050No OrderNo Reorder

Note: The formulas in these examples are provided for illustrative purposes and may require modification based on your specific Excel setup and data range. Ensure you reference the correct sheet names and cell ranges in your Excel spreadsheet.

Part 3. Tips and Tricks

• When using the LOOKUP function for exact matches, ensure that the `lookup_array` is sorted in ascending order to obtain accurate results.
• If you are looking for approximate matches using the LOOKUP function, make sure that the `lookup_array` is in ascending order; otherwise, the results may not be as expected.
• Consider using the VLOOKUP or HLOOKUP functions when searching for larger tables or matrices values. These functions are often more efficient for such scenarios.
• Combine the LOOKUP function with IF, SUM, or AVERAGE functions to perform more complex calculations and data manipulations.
• Always review the results returned by the LOOKUP function and verify that they match your expectations, especially when dealing with large datasets.