VLOOKUP Function in Excel

📊 Example 4: Customer Data Table (Sheet: “CustomerData”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to find the total purchase amount for customers using the VLOOKUP function. The formula in cell B2 would be “=VLOOKUP(A2, CustomerData!$A$2:$C$4, 3, FALSE)”. The Function searches for the “Customer ID” (102) in the leftmost column (A2:A4) of the “CustomerData” sheet and returns the corresponding value from the third column (Total Purch), which is “$250”.

📊 Example 5: Product Inventory Table (Sheet: “ProductInventory”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to determine whether a product has more than 10 units available in inventory using the VLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(VLOOKUP(A2, ProductInventory!$A$2:$C$4, 3, FALSE)>10, “Yes”, “No”)”. The VLOOKUP function searches for “Tablet” in the leftmost column (A2:A4) of the “ProductInventory” sheet and returns the corresponding value from the third column (Available Qty), which is 30. The IF function then checks if the quantity exceeds 10 and returns “Yes” if true and “No” if false.

📊 Example 6: Sales Transactions Table (Sheet: “SalesTransactions”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to calculate the total amount for each sales transaction using the VLOOKUP function nested with the SUM function. The formula in cell B2 would be “=SUM(VLOOKUP(A2, SalesTransactions!$A$2:$C$4, 3, FALSE), VLOOKUP(A2, SalesTransactions!$A$2:$C$4, 3, FALSE))”. The VLOOKUP function searches for the TransactionID (2) in the leftmost column (A2:A4) of the “SalesTransactions” sheet and returns the corresponding value from the third column (Amount), which is $80. The SUM function then adds up the values for the same TransactionID (2) and returns the total amount of $80.

📊 Example 7: Employee Bonus Table (Sheet: “EmployeeBonus”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: We want to find the employee bonus percentage using the VLOOKUP function in this example. The formula in cell B2 would be “=VLOOKUP(A2, EmployeeBonus!$A$2:$C$4, 3, FALSE)”. The Function searches for the “Employee ID” (102) in the leftmost column (A2:A4) of the “EmployeeBonus” sheet and returns the corresponding value from the third column (Bonus %), which is 10%.

📊 Example 8: Order Tracking Table (Sheet: “OrderTracking”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to check whether an order is “Shipped” using the VLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(VLOOKUP(A2, OrderTracking!$A$2:$C$4, 3, FALSE)=”Shipped”, “Yes”, “No”)”. The VLOOKUP function searches for OrderID (3) in the leftmost column (A2:A4) of the “OrderTracking” sheet and returns the corresponding value from the third column (Status), which is “Delivered”. The IF function then checks if the status is “Shipped” and returns “Yes” if true and “No” if false.

📊 Example 9: Expense Report Table (Sheet: “ExpenseReport”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to check if an expense category is approved and return the amount if approved using the VLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(VLOOKUP(A2, ExpenseReport!$A$2:$C$4, 3, FALSE)=”Yes”, VLOOKUP(A2, ExpenseReport!$A$2:$C$4, 2, FALSE), “Not Approved”)”. The VLOOKUP function searches for “Food” in the leftmost column (A2:A4) of the “ExpenseReport” sheet and returns the corresponding value from the third column (Approved), which is “No”. The IF function then checks if the status is “Yes” and returns the corresponding amount from the second column (Amount), which is “$50”, if true. If false, it returns “Not Approved”.

📊 Example 10: Student Grades Table (Sheet: “StudentGrades”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to find a student’s grade using the VLOOKUP function. The formula in cell B2 would be “=VLOOKUP(A2, StudentGrades!$A$2:$C$4, 3, FALSE)”. The Function searches for the “Student ID” (102) in the leftmost column (A2:A4) of the “StudentGrades” sheet and returns the corresponding value from the third column (Grade), which is 92.

📊 Example 11: Product Pricing Table (Sheet: “ProductPricing”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to find the price of a product using the VLOOKUP function and handle the case where the Product ID is not found using the IFERROR function. The formula in cell B2 would be “=IFERROR(VLOOKUP(A2, ProductPricing!$A$2:$C$4, 3, FALSE), “Not Found”)”. The VLOOKUP function searches for “104” in the leftmost column (A2:A4) of the “ProductPricing” sheet and returns an #N/A error since the Product ID does not exist. The IFERROR function catches the error and returns “Not Found” instead of the error message.

📊 Example 12: Monthly Sales Data Table (Sheet: “MonthlySales”)

Table 2 (VLOOKUP Result) – Sheet: “ResultsSheet”:

Explanation: In this example, we want to calculate each month’s total earnings (commission) using the VLOOKUP function nested with the SUM function. The formula in cell B2 would be “=VLOOKUP(A2, MonthlySales!$A$2:$C$4, 2, FALSE) + VLOOKUP(A2, MonthlySales!$A$2:$C$4, 3, FALSE)”. The VLOOKUP function searches for “Feb” in the leftmost column (A2:A4) of the “MonthlySales” sheet and returns the corresponding value from the second column (Total Sales), which is $8,000. It then adds this value to the result of the second VLOOKUP function that returns the commission for February, which is $800. The final result is the total earnings for February, which is $8,800.

These examples demonstrate how the VLOOKUP function can be nested with various other parts to perform different tasks and calculations in Microsoft Excel. By combining VLOOKUP with other functions, you can efficiently analyze and retrieve data based on specific criteria, making Excel a powerful tool for data analysis and decision-making.