# VLOOKUP Function in Excel

Part 1: Introduceย

๐ Definition: The VLOOKUP function in Microsoft Excel is a powerful tool for data analysis, searching, and retrieval. It stands for “Vertical Lookup” and allows you to quickly find specific information within a dataset.

๐ฏ Purpose: The main purpose of the VLOOKUP function is to search for a value in the leftmost column of a data table and return a corresponding value from a specified column. It is commonly used in various business scenarios to retrieve relevant information based on specific criteria.

๐ป Syntax & Arguments:

syntax
`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) `

๐ Explain the Arguments in the Function:

• `lookup_value`: This is the value you want to search for in the leftmost column of the `table_array`.
• `table_array`: This range contains the data you want to search through. The leftmost column of this range should have the `lookup_valuedata you want to retrieve `in columns to the right.
• `col_index_num`: This is the column number (starting from 1)ย  ย `table_array` from which you want to retrieve the result.
• `[range_lookup]`: An optional argument. If set to `TRUE` or omitted, the Function performs an approximate match. If assigned`FALSE`, it performs an exact match. (Recommended to use `FALSE` for most cases.)

๐ Return Value: The VLOOKUP function returns the value found in the specified column (`col_index_num`) that corresponds to the `lookup_value`.

๐ฌ Remarks:

• It is essential to sort the leftmost columnย  `table_array` in ascending order using an exact match (range_lookup = FALSE).
• If the `lookup_value` The Function is not found in the leftmost column and returns an #N/A error.

๐ Part 2: Examples ๐

๐ Example 1: Sales Data Table (Sheet: “SalesData”)

ABC
1ProductPriceSales
2Apple\$150
3Orange\$1.530
4Banana\$0.7540

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

AB
1Search ValueResult
2Orange=VLOOKUP(A2, SalesData!\$A\$2:\$C\$4, 2, FALSE)
3Banana=VLOOKUP(A3, SalesData!\$A\$2:\$C\$4, 2, FALSE)
4Apple=VLOOKUP(A4, SalesData!\$A\$2:\$C\$4, 2, FALSE)

Explanation: In this example, we want to find the price of an “Orange” using the VLOOKUP function. The formula in cell B2 would be “=VLOOKUP(A2, SalesData!\$A\$2:\$C\$4, 2, FALSE)”. The Function searches for “Orange” in the leftmost column (A2:A4) of the “SalesData” sheet and returns the corresponding value from the second column (Price), which is “\$1.5”.

๐ Example 2: Employee Data Table (Sheet: “employee data”)

ABC
1Employee IDNameDepartment
2101JohnHR
3102SarahIT
4103MichaelFinance

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

AB
1Search ValueResult
2Sarah=VLOOKUP(A2, EmployeeData!\$A\$2:\$C\$4, 3, FALSE)
3103=VLOOKUP(A3, EmployeeData!\$A\$2:\$C\$4, 2, FALSE)
4101=VLOOKUP(A4, EmployeeData!\$A\$2:\$C\$4, 3, FALSE)

Explanation: In this example, we want to find the department of “Sarah” using the VLOOKUP function. The formula in cell B2 would be “=VLOOKUP(A2, EmployeeData!\$A\$2:\$C\$4, 3, FALSE)”. The Function searches for “Sarah” in the leftmost column (A2:A4) of the “EmployeeData” sheet and returns the corresponding value from the third column (Department), which is “IT”.

๐ Example 3: Inventory Data Table (Sheet: “InventoryData”)

ABC
1ItemQuantityLocation
2Pen100Shelf 1
3Notebook50Shelf 2
4Eraser75Shelf 1

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

AB
1Search ValueResult
2Eraser=VLOOKUP(A2, InventoryData!\$A\$2:\$C\$4, 2, FALSE)
3Pen=VLOOKUP(A3, InventoryData!\$A\$2:\$C\$4, 2, FALSE)
4Notebook=VLOOKUP(A4, InventoryData!\$A\$2:\$C\$4, 2, FALSE)

Explanation: In this example, we want to find the quantity of “Eraser” using the VLOOKUP function. The formula in cell B2 would be “=VLOOKUP(A2, InventoryData!\$A\$2:\$C\$4, 2, FALSE)”. The Function searches for “Eraser” in the leftmost column (A2:A4) of the “InventoryData” sheet and returns the corresponding value from the second column (Quantity), which is “75”.

๐ Example 4: Customer Data Table (Sheet: “CustomerData”)

ABC
1Customer IDNameTotal Purch
2101John\$120
3102Sarah\$250
4103Michael\$80

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

AB
1Search ValueResult
2102=VLOOKUP(A2, CustomerData!\$A\$2:\$C\$4, 3, FALSE)
3101=VLOOKUP(A3, CustomerData!\$A\$2:\$C\$4, 3, FALSE)
4103=VLOOKUP(A4, CustomerData!\$A\$2:\$C\$4, 3, FALSE)

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”)

ABC
1Product IDProduct NameAvailable Qty
2101Laptop20
3102Smartphone15
4103Tablet30

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

AB
1Search ValueResult
2Tablet=IF(VLOOKUP(A2, ProductInventory!\$A\$2:\$C\$4, 3, FALSE)>10, “Yes”, “No”)
3101=IF(VLOOKUP(A3, ProductInventory!\$A\$2:\$C\$4, 3, FALSE)>10, “Yes”, “No”)
4102=IF(VLOOKUP(A4, ProductInventory!\$A\$2:\$C\$4, 3, FALSE)>10, “Yes”, “No”)

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”)

ABC
1TransactionIDCustomerIDAmount
21102\$150
32101\$80
43103\$200

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

AB
1Search ValueResult
22=SUM(VLOOKUP(A2, SalesTransactions!\$A\$2:\$C\$4, 3, FALSE), VLOOKUP(A2, SalesTransactions!\$A\$2:\$C\$4, 3, FALSE))
31=SUM(VLOOKUP(A3, SalesTransactions!\$A\$2:\$C\$4, 3, FALSE), VLOOKUP(A3, SalesTransactions!\$A\$2:\$C\$4, 3, FALSE))
43=SUM(VLOOKUP(A4, SalesTransactions!\$A\$2:\$C\$4, 3, FALSE), VLOOKUP(A4, SalesTransactions!\$A\$2:\$C\$4, 3, FALSE))

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”)

ABC
1Employee IDNameBonus (%)
2101John5
3102Sarah10
4103Michael8

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

AB
1Search ValueResult
2102=VLOOKUP(A2, EmployeeBonus!\$A\$2:\$C\$4, 3, FALSE)
3101=VLOOKUP(A3, EmployeeBonus!\$A\$2:\$C\$4, 3, FALSE)
4103=VLOOKUP(A4, EmployeeBonus!\$A\$2:\$C\$4, 3, FALSE)

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”)

ABC
1OrderIDProductIDStatus
21101Shipped
32102Pending
43103Delivered

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

AB
1Search ValueResult
23=IF(VLOOKUP(A2, OrderTracking!\$A\$2:\$C\$4, 3, FALSE)=”Shipped”, “Yes”, “No”)
31=IF(VLOOKUP(A3, OrderTracking!\$A\$2:\$C\$4, 3, FALSE)=”Shipped”, “Yes”, “No”)
42=IF(VLOOKUP(A4, OrderTracking!\$A\$2:\$C\$4, 3, FALSE)=”Shipped”, “Yes”, “No”)

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”)

ABC
1CategoryAmountApproved
2Travel\$250Yes
3Office\$80Yes
4Food\$50No

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

AB
1Search ValueResult
2Food=IF(VLOOKUP(A2, ExpenseReport!\$A\$2:\$C\$4, 3, FALSE)=”Yes”, VLOOKUP(A2, ExpenseReport!\$A\$2:\$C\$4, 2, FALSE), “Not Approved”)
3Travel=IF(VLOOKUP(A3, ExpenseReport!\$A\$2:\$C\$4, 3, FALSE)=”Yes”, VLOOKUP(A3, ExpenseReport!\$A\$2:\$C\$4, 2, FALSE), “Not Approved”)
4Office=IF(VLOOKUP(A4, ExpenseReport!\$A\$2:\$C\$4, 3, FALSE)=”Yes”, VLOOKUP(A4, ExpenseReport!\$A\$2:\$C\$4, 2, FALSE), “Not Approved”)

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”.

ABC
2101Math85
3102Science92
4103English78

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

AB
1Search ValueResult

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”)

ABC
1Product IDProduct NamePrice
2101Laptop\$800
3102Smartphone\$500
4103Tablet\$350

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

AB
1Search ValueResult

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”)

ABC
1MonthTotal SalesCommission
2Jan\$10,000\$1,000
3Feb\$8,000\$800
4Mar\$12,000\$1,200

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

AB
1Search ValueResult
2Feb=VLOOKUP(A2, MonthlySales!\$A\$2:\$C\$4, 2, FALSE) + VLOOKUP(A2, MonthlySales!\$A\$2:\$C\$4, 3, FALSE)
3Jan=VLOOKUP(A3, MonthlySales!\$A\$2:\$C\$4, 2, FALSE) + VLOOKUP(A3, MonthlySales!\$A\$2:\$C\$4, 3, FALSE)
4Mar=VLOOKUP(A4, MonthlySales!\$A\$2:\$C\$4, 2, FALSE) + VLOOKUP(A4, MonthlySales!\$A\$2:\$C\$4, 3, FALSE)

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.

๐ Part 3: Tips and Tricks ๐

1. Always use `FALSE` as the last argument in the VLOOKUP function for exact matches to avoid unexpected results.
2. If you `lookup_value` is not found, consider using the `IFERROR` Function to handle the #N/A error gracefully.
3. Ensure that the leftmost column of your `table_array` is sorted in ascending order for exact matches.
4. For faster calculations, try to limit the sizeย  `table_array` by using named ranges.
5. Combine VLOOKUP with other functions like `IF`, `SUM`, or `AVERAGE` for more advanced data analysis tasks.