VBA if and vlookup on multiple columns

3 min read 28-10-2024

the ifix

When working with Excel, the combination of VBA (Visual Basic for Applications) can significantly enhance your data manipulation capabilities. One common task involves using the IF statement alongside VLOOKUP to retrieve data from multiple columns efficiently. In this article, we will break down the process of using these functions in VBA and provide practical examples.

Problem Scenario

Let's say you have a dataset that consists of various product details, including Product ID, Name, and Price. You want to create a function that checks if a specific Product ID exists in this dataset. If it does, you also want to retrieve the corresponding Name and Price. The following is a basic example of how this can be accomplished using VBA:

Sub VLookupExample()
    Dim ProductID As String
    Dim Result As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ProductID = InputBox("Enter Product ID:")
    Result = Application.VLookup(ProductID, ws.Range("A2:C10"), 2, False)
    
    If Not IsError(Result) Then
        MsgBox "Product Name: " & Result
    Else
        MsgBox "Product ID not found."
    End If
End Sub

Analyzing the Code

In this snippet:

We declare variables to hold the product ID, the result of the VLOOKUP, and the worksheet object.
We prompt the user to enter a Product ID using an input box.
We use Application.VLookup to search for the Product ID in the range from A2 to C10, returning the second column (Product Name).
Finally, we use an IF statement to check if the VLOOKUP returned an error (i.e., the Product ID was not found) and display a message accordingly.

Expanding Functionality with VLOOKUP on Multiple Columns

In many cases, you may need to retrieve data from multiple columns instead of just one. Let's enhance our original example to not only fetch the Product Name but also the Product Price.

Here's an updated version of the code:

Sub VLookupMultipleColumns()
    Dim ProductID As String
    Dim NameResult As Variant
    Dim PriceResult As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ProductID = InputBox("Enter Product ID:")
    NameResult = Application.VLookup(ProductID, ws.Range("A2:C10"), 2, False)
    PriceResult = Application.VLookup(ProductID, ws.Range("A2:C10"), 3, False)
    
    If Not IsError(NameResult) Then
        MsgBox "Product Name: " & NameResult & vbNewLine & "Product Price: " & PriceResult
    Else
        MsgBox "Product ID not found."
    End If
End Sub

Explanation of the Enhanced Code

In the improved version:

We introduced an additional variable PriceResult to hold the output of the second VLOOKUP for the price.
We specify column indexes to retrieve both the Name (column 2) and Price (column 3).
The message box now displays both the Product Name and Product Price in a single message.

Practical Example

Imagine you have a product list in your Excel worksheet (Sheet1) that looks like this:

Product ID	Product Name	Price
101	Widget A	$25
102	Widget B	$30
103	Widget C	$45

When you run the VLookupMultipleColumns subroutine and input "102", the message box will return:

Product Name: Widget B
Product Price: $30

Conclusion

Using VBA with IF and VLOOKUP across multiple columns can streamline the process of data retrieval and manipulation in Excel. This combination is particularly useful in financial analysis, inventory management, and any task where quick data lookups are essential.

Useful Resources

By mastering these techniques, you can enhance your productivity and make your Excel applications more powerful and user-friendly.