**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:**

`=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”)**

A | B | C | |
---|---|---|---|

1 | Product | Price | Sales |

2 | Apple | $1 | 50 |

3 | Orange | $1.5 | 30 |

4 | Banana | $0.75 | 40 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | Orange | =VLOOKUP(A2, SalesData!$A$2:$C$4, 2, FALSE) |

3 | Banana | =VLOOKUP(A3, SalesData!$A$2:$C$4, 2, FALSE) |

4 | Apple | =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”)**

A | B | C | |
---|---|---|---|

1 | Employee ID | Name | Department |

2 | 101 | John | HR |

3 | 102 | Sarah | IT |

4 | 103 | Michael | Finance |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | Sarah | =VLOOKUP(A2, EmployeeData!$A$2:$C$4, 3, FALSE) |

3 | 103 | =VLOOKUP(A3, EmployeeData!$A$2:$C$4, 2, FALSE) |

4 | 101 | =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”)**

A | B | C | |
---|---|---|---|

1 | Item | Quantity | Location |

2 | Pen | 100 | Shelf 1 |

3 | Notebook | 50 | Shelf 2 |

4 | Eraser | 75 | Shelf 1 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | Eraser | =VLOOKUP(A2, InventoryData!$A$2:$C$4, 2, FALSE) |

3 | Pen | =VLOOKUP(A3, InventoryData!$A$2:$C$4, 2, FALSE) |

4 | Notebook | =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”)**

A | B | C | |
---|---|---|---|

1 | Customer ID | Name | Total Purch |

2 | 101 | John | $120 |

3 | 102 | Sarah | $250 |

4 | 103 | Michael | $80 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | 102 | =VLOOKUP(A2, CustomerData!$A$2:$C$4, 3, FALSE) |

3 | 101 | =VLOOKUP(A3, CustomerData!$A$2:$C$4, 3, FALSE) |

4 | 103 | =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”)**

A | B | C | |
---|---|---|---|

1 | Product ID | Product Name | Available Qty |

2 | 101 | Laptop | 20 |

3 | 102 | Smartphone | 15 |

4 | 103 | Tablet | 30 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | Tablet | =IF(VLOOKUP(A2, ProductInventory!$A$2:$C$4, 3, FALSE)>10, “Yes”, “No”) |

3 | 101 | =IF(VLOOKUP(A3, ProductInventory!$A$2:$C$4, 3, FALSE)>10, “Yes”, “No”) |

4 | 102 | =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”)**

A | B | C | |
---|---|---|---|

1 | TransactionID | CustomerID | Amount |

2 | 1 | 102 | $150 |

3 | 2 | 101 | $80 |

4 | 3 | 103 | $200 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | 2 | =SUM(VLOOKUP(A2, SalesTransactions!$A$2:$C$4, 3, FALSE), VLOOKUP(A2, SalesTransactions!$A$2:$C$4, 3, FALSE)) |

3 | 1 | =SUM(VLOOKUP(A3, SalesTransactions!$A$2:$C$4, 3, FALSE), VLOOKUP(A3, SalesTransactions!$A$2:$C$4, 3, FALSE)) |

4 | 3 | =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”)**

A | B | C | |
---|---|---|---|

1 | Employee ID | Name | Bonus (%) |

2 | 101 | John | 5 |

3 | 102 | Sarah | 10 |

4 | 103 | Michael | 8 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | 102 | =VLOOKUP(A2, EmployeeBonus!$A$2:$C$4, 3, FALSE) |

3 | 101 | =VLOOKUP(A3, EmployeeBonus!$A$2:$C$4, 3, FALSE) |

4 | 103 | =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”)**

A | B | C | |
---|---|---|---|

1 | OrderID | ProductID | Status |

2 | 1 | 101 | Shipped |

3 | 2 | 102 | Pending |

4 | 3 | 103 | Delivered |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | 3 | =IF(VLOOKUP(A2, OrderTracking!$A$2:$C$4, 3, FALSE)=”Shipped”, “Yes”, “No”) |

3 | 1 | =IF(VLOOKUP(A3, OrderTracking!$A$2:$C$4, 3, FALSE)=”Shipped”, “Yes”, “No”) |

4 | 2 | =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”)**

A | B | C | |
---|---|---|---|

1 | Category | Amount | Approved |

2 | Travel | $250 | Yes |

3 | Office | $80 | Yes |

4 | Food | $50 | No |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | Food | =IF(VLOOKUP(A2, ExpenseReport!$A$2:$C$4, 3, FALSE)=”Yes”, VLOOKUP(A2, ExpenseReport!$A$2:$C$4, 2, FALSE), “Not Approved”) |

3 | Travel | =IF(VLOOKUP(A3, ExpenseReport!$A$2:$C$4, 3, FALSE)=”Yes”, VLOOKUP(A3, ExpenseReport!$A$2:$C$4, 2, FALSE), “Not Approved”) |

4 | Office | =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”.

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

A | B | C | |
---|---|---|---|

1 | Student ID | Subject | Grade |

2 | 101 | Math | 85 |

3 | 102 | Science | 92 |

4 | 103 | English | 78 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | 102 | =VLOOKUP(A2, StudentGrades!$A$2:$C$4, 3, FALSE) |

3 | 101 | =VLOOKUP(A3, StudentGrades!$A$2:$C$4, 3, FALSE) |

4 | 103 | =VLOOKUP(A4, StudentGrades!$A$2:$C$4, 3, FALSE) |

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

A | B | C | |
---|---|---|---|

1 | Product ID | Product Name | Price |

2 | 101 | Laptop | $800 |

3 | 102 | Smartphone | $500 |

4 | 103 | Tablet | $350 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | 104 | =IFERROR(VLOOKUP(A2, ProductPricing!$A$2:$C$4, 3, FALSE), “Not Found”) |

3 | 101 | =IFERROR(VLOOKUP(A3, ProductPricing!$A$2:$C$4, 3, FALSE), “Not Found”) |

4 | 102 | =IFERROR(VLOOKUP(A4, ProductPricing!$A$2:$C$4, 3, FALSE), “Not Found”) |

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

A | B | C | |
---|---|---|---|

1 | Month | Total Sales | Commission |

2 | Jan | $10,000 | $1,000 |

3 | Feb | $8,000 | $800 |

4 | Mar | $12,000 | $1,200 |

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

A | B | |
---|---|---|

1 | Search Value | Result |

2 | Feb | =VLOOKUP(A2, MonthlySales!$A$2:$C$4, 2, FALSE) + VLOOKUP(A2, MonthlySales!$A$2:$C$4, 3, FALSE) |

3 | Jan | =VLOOKUP(A3, MonthlySales!$A$2:$C$4, 2, FALSE) + VLOOKUP(A3, MonthlySales!$A$2:$C$4, 3, FALSE) |

4 | Mar | =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 ๐**

- Always use
`FALSE`

as the last argument in the VLOOKUP function for exact matches to avoid unexpected results. - If you
`lookup_value`

is not found, consider using the`IFERROR`

Function to handle the #N/A error gracefully. - Ensure that the leftmost column of your
`table_array`

is sorted in ascending order for exact matches. - For faster calculations, try to limit the sizeย
`table_array`

by using named ranges. - Combine VLOOKUP with other functions like
`IF`

,`SUM`

, or`AVERAGE`

for more advanced data analysis tasks.