[Excel VBA] How to Perform Modern Lookups with the XLOOKUP Function

For many years, the VLOOKUP function was the standard for lookups in Excel, but it had several weaknesses. For example, the search column had to be on the far left, and it returned errors when values were not found.

In Excel for Microsoft 365 and Excel 2021, the XLOOKUP function was introduced. It is a powerful and flexible successor that overcomes these limitations.

Naturally, you can also use XLOOKUP in VBA. This article explains how to use WorksheetFunction.XLookup in VBA to implement smart lookups that include built-in error handling.

Important: Requirements

To use this technique, your version of Excel must support the XLOOKUP function (Microsoft 365 or Excel 2021 and later). Please note that this code will not run on older versions of Excel.

Basic Syntax for XLOOKUP in VBA

The syntax for calling XLOOKUP from VBA is the same as using it on a worksheet.

WorksheetFunction.XLookup(lookup_value, lookup_array, return_array, [if_not_found])

• lookup_value: The value you want to search for.
• lookup_array: The range (one row or one column) to search.
• return_array: The range containing the value(s) you want to return. You can specify multiple columns.
• [if_not_found] (Optional): The value to return if no match is found. By specifying this, you can avoid the complex error handling required with VLOOKUP.

The VBA Code

The following code searches for a specific product code in a table on the “ProductMaster” sheet and retrieves both the Product Name and Price at once.

Sub XLookupInVBA()

    ' Declare variables
    Dim lookupValue As String
    Dim lookupArray As Range
    Dim returnArray As Range
    Dim result As Variant

    '--- Settings ---
    lookupValue = "P-003" ' The product code to search for
    
    With ThisWorkbook.Worksheets("ProductMaster")
        ' Column to search (Column B)
        Set lookupArray = .Range("B2:B100")
        ' Columns to return (Columns C and D)
        Set returnArray = .Range("C2:D100")
    End With
    '--- End Settings ---
    
    ' --- 1. Get data using XLOOKUP ---
    ' Specify "Not Found" string to be returned if no match exists
    result = WorksheetFunction.XLookup(lookupValue, lookupArray, returnArray, "Not Found")

    ' --- 2. Check and display the result ---
    ' Check if the result is an array to determine success
    If IsArray(result) Then
        ' Found (Since return range is multiple columns, result is an array)
        MsgBox "Search result found." & vbCrLf & vbCrLf & _
               "Product Name: " & result(1, 1) & vbCrLf & _
               "Price: " & result(1, 2) & " Yen", vbInformation
    Else
        ' Not Found (Returns the string "Not Found")
        MsgBox "Product Code '" & lookupValue & "' was not found.", vbExclamation
    End If

End Sub

Key Points of the Code

1. WorksheetFunction.XLOOKUP

result = WorksheetFunction.XLookup(lookupValue, lookupArray, returnArray, "Not Found")

We call the function via WorksheetFunction. One of the biggest advantages here is the 4th argument, [if_not_found].

With VLOOKUP, if a value was not found, it caused a runtime error, requiring error handling like On Error or IsError. With XLOOKUP, providing this argument ensures the function returns your specified value (in this case, the string “Not Found”) instead of crashing the code.

2. Checking the Return Value (IsArray)

If IsArray(result) Then ...

In this example, the returnArray spans two columns (Name and Price). Therefore, if XLOOKUP succeeds, it returns an array containing two values.

On the other hand, if the value is not found, it returns the single string “Not Found” (as defined in the 4th argument).

By using the IsArray function to check the result, we can smartly determine whether the search was successful without complex error trapping.

Summary

In modern Excel environments where XLOOKUP is available, lookups in VBA are much safer and easier than before.

• Using WorksheetFunction.XLookup allows for more flexible searches than VLOOKUP.
• Specifying the 4th argument [if_not_found] eliminates the need for complex error handling logic.
• You can retrieve multiple values as an array by specifying multiple columns in the return range.

If you are writing new code for an environment that supports it, I highly recommend using XLOOKUP.