Excel Function to Return Value from Second Column on Match

Overview

This Excel function checks if there is a match in the first column of a specified range and returns the corresponding value from the second column. The function utilizes Excel's built-in VLOOKUP to achieve this efficiently.

Function Definition

Function Signature

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

Parameters

• lookup_value: The value you want to search for in the first column (the key).
• table_array: The range of cells that contains the data (including both columns).
• col_index_num: The number of the column in the table from which to retrieve the value (2 for the second column).
• [range_lookup]: Optional. TRUE for an approximate match or FALSE for an exact match.

Return Type

• Returns the corresponding value from the second column if a match is found; otherwise, it returns #N/A.

Exception Handling

• If lookup_value is not found, the function will return #N/A.

Example Code

In a working Excel environment, use the following function formula:

=VLOOKUP(A1, B:C, 2, FALSE)

Description of Example

• A1: This is where you input the value you are searching for.
• B:C: This is the range containing your data, where the first column (B) is where VLOOKUP will look for a match and the second column (C) is where it will retrieve the value.
• 2: This indicates that we want the value from the second column of the specified range.
• FALSE: This ensures that we are looking for an exact match.

Practical Usage Example

Suppose you have the following data in columns B and C:

If you enter Code2 in cell A1, the function would return Item B when using:

=VLOOKUP(A1, B:C, 2, FALSE)

Step-by-Step Implementation in Excel

1. Open an Excel worksheet.

2. Populate cells B1 to C3 with the example data provided.

3. Enter the lookup value (e.g., Code2) in cell A1.

4. In another cell, (e.g., A2), input the VLOOKUP formula:

   =VLOOKUP(A1, B:C, 2, FALSE)

5. Upon pressing Enter, A2 will display Item B, assuming Code2 is present in column B.

Key Takeaways

• Efficiency: This method efficiently retrieves data based on a lookup.
• Ease of Use: VLOOKUP is straightforward and commonly used in data analysis.
• Adaptability: You can easily modify the lookup_value and table_array to fit different datasets.

For deeper insights and more complex data manipulations, consider exploring courses offered by the Enterprise DNA Platform.