Excel VLOOKUP Function

The term VLOOKUP stands for Vertical Lookup. It is designed to search for a specific value in the first column of a table (lookup column) and retrieve corresponding data from a different column in the same row.

Syntax of VLOOKUP Formula:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value we want to search for (e.g., a specific ID, name, or product code).
• table_array: The range of cells that contains the data (the table we're searching through).
• col_index_num: The column number (from the table array) that contains the data we want to retrieve. The first column is 1, the second is 2, and so on.
• [range_lookup] (optional): If TRUE (or omitted), it searches for an approximate match. If FALSE, it searches for an exact match.
• TRUE indicates an approximate match, and it's the default value if this argument is omitted.
• FALSE indicates an exact match.

Note: If nothing is specified by the user, by default this value is set to be 'True' and it searches for an approximate match. This argument is completely optional.

Applying VLOOKUP Step-by-Step

We can use VLOOKUP with practical examples.

Step 1: Preparing our Data

Make sure our data is arranged with the lookup column (the column we will search) as the first column of our table. For example, if we’re looking up product prices by ID, the IDs should be in the first column:

• List all product IDs in the first column (A).
• Product Name in second column (B).
• Ensure the corresponding prices are in a column to the right of the IDs and Product Name, here we have mentioned that in column (C).

Note: Having the lookup column as the first column ensures VLOOKUP works correctly. Incorrect table organization can lead to errors or incorrect results.

Step 2: Enter the VLOOKUP Formula

Select a cell where we want the price to appear when we type a product ID.

• Select a Cell for the Result: Click in the cell where we want the VLOOKUP result to appear, here we have selected D2.
• Start the Formula: Type =VLOOKUP( in cell D2.

Step 3: Define the Lookup Value

Enter the value we want to search for in the formula:

• Use a cell reference, like A2, or type a value directly in quotes, such as "001".
• Example: If searching for a product ID in cell D2, start the formula as:
  =VLOOKUP(A2, or =VLOOKUP("001",
• Add a comma after the lookup value to move to the next part of the formula.

Step 4: Specify the Table Array

We define the range where VLOOKUP searches for our data, ensuring it includes all relevant columns. This step is crucial for accurate results, as it tells Excel exactly where to look. Let’s set it up carefully to match our table structure.

• Select the data range where we want to search, e.g., A1:C4(IDs in column A and Prices in column C).
• Highlight the range by clicking and dragging.
• Add a comma after selecting the table array.

Step 5: Indicate the Column Index Number

We choose the column from which VLOOKUP retrieves our data, a critical decision for accurate results. This number tells Excel where to pull the information once a match is found. Let’s select it with precision to match our table layout.

• Enter the column number from which we want to retrieve the data.
• For example, if prices are in the third column (C), type 3 in the formula: =VLOOKUP(D2, A1:C4, 3,.
• Add a comma after the column number.

Purpose of col_index_num: The col_index_num in a VLOOKUP formula specifies which column in the table_array to pull the data from, once a match is found for the lookup_value in the first column of that array.

Why Use 3?
In the range A1:C4:

• Column A has Product IDs.
• Column B has Product Names.
• Column C has Prices.

The number 3 tells Excel to return data from the third column (Prices).

This instructs Excel to look within the range A1 to C4 and return the value from the third column of the range when it finds a match.

Step 6: Choose the Range Lookup Type

We determine how VLOOKUP matches our data, a key factor in getting the right results. This choice ensures our search aligns with our needs, whether exact or approximate. Let’s set it accurately to avoid errors.

• Decide if we need an exact match or an approximate match for our search.
• For exact matches (e.g., finding the exact price for a product ID), type FALSE in the formula.
• Add a closing parenthesis ) to complete the formula.

Purpose of range_lookup:

• FALSE: Ensures VLOOKUP finds an exact match for the lookup value.
• TRUE: Allows approximate matches, typically used when the data is sorted.

Why Use FALSE?

Using FALSE ensures VLOOKUP only returns a value if the exact Product ID exists in the first column (e.g., column A).

• If no match is found, it shows #N/A, preventing incorrect results.
• This is important for precise searches, like finding exact prices or IDs.

By setting the parameter to FALSE, our formula ensures accuracy and avoids errors caused by approximate matches.

This ensures that the exact Product ID entered in cell D2 is matched in column A, and the corresponding price from column C is returned. If no exact match is found, the function will display an #N/A error, indicating the ID is not in the list. This is safer than returning an incorrect or approximate price.

Step 7: Complete and Execute the Formula

We finalize our VLOOKUP formula and see the results come to life in our spreadsheet. This step confirms our setup works, delivering the data we need. Let’s execute it with confidence.

1. Press Enter: Our complete formula in cell D2 should look like this:

=VLOOKUP(A2, A1:C4, 3, FALSE)

2.View the Result: After pressing Enter, cell D2 should display $1, which is the price of the product with ID 001.

VLOOKUP between two Excel Spreadsheets

Using the VLOOKUP function to connect data between two Excel sheets within the same workbook is a efficient way to improve efficiency and smooth our workflow. Here’s an easy step-by-step guide to help we use VLOOKUP across two sheets.

Example:

Imagine we have an Excel workbook with two sheets.

Sheet1 ("Employee Info") contains a list of employee IDs and their names.

Sheet2 ("Contact Details") contains a list of employee IDs and their corresponding email addresses.

Goal:

Use VLOOKUP to display employee email addresses in Sheet1 based on their IDs.

Step 1: Set Up VLOOKUP in Sheet1

Navigate to Sheet1 ("Employee Info") and click on cell C2 (right next to the first Employee ID).

Step 2: Enter the VLOOKUP Formula

Type in the following formula in cell C2 of Sheet1:

Syntax:

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

Here’s a simple explanation of each part of the formula:

• A2: Refers to the cell in Sheet1 containing the Employee ID we want to look up.
• Sheet2!A:B: Tells Excel to search in columns A to B of Sheet2. The "Sheet2!" notation indicates the data is on another sheet.
• 2: Specifies that the result we want (the email address) is in the second column of the range in Sheet2.
• FALSE: Ensures the formula returns an exact match for the Employee ID.

Step 3: Apply and Copy the Formula

We bring our VLOOKUP formula to life and extend it across our data, ensuring all rows update correctly. This step links our sheets seamlessly, saving us time. Let’s apply it with care.

• Press Enter: After typing the formula in cell C2, press Enter. The result, "john.doe@email.com" will appear for Employee ID 101.
• Copy the Formula: Click on cell C2.
• Drag the Fill Handle: Use the small square at the bottom-right corner of the cell. Drag it down to copy the formula to the other cells in Column C.
• Auto-Adjust: Excel will automatically adjust the formula for each row, showing the corresponding email addresses for the other Employee IDs (C3, C4, etc).

Step 4: Verify the Results

Check that the correct email addresses appear next to the corresponding employee names in Sheet1. The complete "Employee Info" table should now look like this:

VLOOKUP between two Workbooks

VLOOKUP is a prominent tool that can pull data from one workbook to another, making it easy to consolidate and analyze information stored in separate files. Here’s a step-by-step guide on how to use VLOOKUP across two Excel workbooks.

Example:

Suppose we're working with sales data and we have two Excel workbooks:

Workbook1 ("Sales Data.xlsx") contains sales transaction details including Transaction IDs and the amounts.

Workbook2 ("Employee Sales.xlsx") contains Transaction IDs and the corresponding employee names who made the sales.

Goal

We want to add a third column to the "Sales Data.xlsx" workbook that displays each employee's name associated with the transactions by linking the data using the Transaction ID.

Step 1: Open Both Workbooks

We begin by accessing the files we need to link, setting the foundation for our VLOOKUP. This ensures all data is ready for seamless integration across workbooks. Let’s open them with purpose.

• Open "Sales Data.xlsx" (Workbook1) and "Employee Sales.xlsx" (Workbook2) simultaneously.
• This ensures Excel can reference the second workbook while creating the VLOOKUP formula.

Step 2: Set Up VLOOKUP in Workbook1

We prepare our primary workbook to apply VLOOKUP, starting with the right cell. This step positions us to link data accurately from the second file. Let’s set it up carefully.

• Go to Workbook1 ("Sales Data.xlsx").
• Select cell C2 (next to the first Transaction ID).

Step 3: Enter the VLOOKUP Formula

Type in the following formula in cell C2 of "Sales Data.xlsx":

=VLOOKUP(A2, '[Employee Sales.xlsx]Sheet1'!$A$1:$B$3, 2, FALSE)

Here's what each part of the formula does:

• A2: The cell contains the first Transaction ID in "Sales Data.xlsx" that we are using as the lookup value.
• '[Employee Sales.xlsx]Sheet1'!$A$1:$B$3: Specifies the range in "Employee Sales.xlsx" where the VLOOKUP should search for the Transaction ID. Ensure that the range includes all necessary rows and columns, and it’s important to use absolute references (with $) so the range does not shift when the formula is copied.
• 2: Indicates that the value we want to return (the employee name) is in the second column of the specified range.
• FALSE: Specifies that we need an exact match for the Transaction ID.

Step 4: Execute and Extend the Formula

Press Enter to execute the formula in cell C2. If correctly entered, cell C2 will now display "John Doe", corresponding to Transaction ID T001.

To apply the same formula to the other transactions:

• Click on cell C2.
• Drag the fill handle down through the column to fill the rest of the cells in Column C with the VLOOKUP formula, adjusting it for the subsequent rows (C3, C4, etc.).

Step 5: Verify the Results

Check that the correct employee names appear next to the corresponding transaction IDs in "Sales Data.xlsx". The complete table should look like this: