LOOKUP Function in Excel

Example 4: Employee Bonus Calculation

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:

Example 5: Exam Grading

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:

Example 6: Discounted Price Calculation

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:

Example 7: Inventory Management

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:

Example 8: Expense Analysis

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:

Example 9: Shipping Fees

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:

Example 10: Sales Commission

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:

Example 11: Test Scores

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:

Example 12: Inventory Reorder Level

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:

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.