URGENT: Early Adopters Should Check Their XLOOKUP Formulas

November 04, 2019

An exciting change happened to the XLOOKUP function in the Office Insiders update that came out November 1, 2019. Many Insiders will be receiving this update as they arrive to work on Monday November 4, 2019.

If you’ve been using the new XLOOKUP function and if you’ve used the Match_Mode argument to look for the value just larger or just smaller, your existing XLOOKUP functions are going to break.

The new change to XLOOKUP: the If_Not_Found argument, which was originally added as an optional sixth argument, has been moved to be the fourth argument.

Consider the following formula, which was previously asking for the next larger match:

=XLOOKUP(A2,H2:H99,J2:J99,1)

When you open a workbook with a formula like this, the formula does not immediately break. Excel’s intelligent recalc won’t recalc the formula until you edit the formula, or until you edit one of the numbers in H2:H99 or J2:J99.

However, once you edit the lookup table, then Excel recalcs all of the XLOOKUP functions that used the table. Before the change, you were asking for an Approximate Match that returned the next larger value. After the change, you are asking for an Exact Match (because your original formula does not have a fifth argument) and also accidentally specifying that if an exact match is not found, then you want to insert a 1 as the result instead.

"It is really an insidious game of whack-a-mole," said Bill Jelen, publisher of MrExcel.com. You press F2 to look at a formula, and the formula stops working. Other formulas in the worksheet might appear to keep working, but they are a ticking time bomb waiting to become wrong when a recalc is triggered."

To see the change happening, watch from the 0:35 to 0:55 second mark in this video:

Watch Video

The Excel team advises that you need to adjust any XLOOKUP formulas that were using the optional arguments. If you have been using XLOOKUP frequently, the following code will examine a workbook and identify possible problem formulas.

Basic Version

Following code looks for the formula cells starting with =XLOOKUP and containing more than 2 commas.

Sub findXLOOKUPs()
Dim sht As Worksheet
Dim cll As Range
Dim foundCells As String

    Set sht = ActiveSheet

    For Each cll In sht.UsedRange
        If cll.HasFormula Then
            If InStr(cll.Formula, "=XLOOKUP") = 1 Then
                If UBound(Split(cll.Formula, ",")) > 2 Then
                    foundCells = foundCells & vbCrLf & cll.Address
                End If
            End If
        End If
    Next cll
    If foundCells = "" Then
        MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors"
    Else
        MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found"
    End If
End Sub

Regex Version

Following code is using Regex to find multiple XLOOKUP functions used in the same formula, or used with other functions may contain additional commas. 

* You need to add Microsoft VBScript Regular Expressions reference in Visual Basic to use this code (Tools > References in VBA).

Sub advancedFindXLOOKUPs()
Dim sht As Worksheet
Dim cll As Range
Dim rgx As RegExp
Dim rMatches As Object
Dim rMatch As Object
Dim foundCells As String

    Set sht = ActiveSheet
    Set rgx = New RegExp
    With rgx
        .Pattern = "XLOOKUP\(([^,\)]*,){3,}[^,]*\)"
        .MultiLine = False
        .IgnoreCase = True
        .Global = True
    End With

    For Each cll In sht.UsedRange
        If cll.HasFormula Then
            Set rMatches = rgx.Execute(cll.Formula)
            If rMatches.Count Then
                For Each rMatch In rMatches
                    'Debug.Print rMatch
                    foundCells = foundCells & vbCrLf & cll.Address
                Next rMatch
            End If
        End If
    Next cll
    
    If foundCells = "" Then
        MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors"
    Else
        MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found"
    End If
End Sub