XLOOKUP backward compatibility

Learn how to use the XLOOKUP function under Excel 2010, Excel 2013, or newer versions. Using a small trick, we keep the backward compatibility. Wait a moment before we take a deep dive into each function. Take a first look at the situation we are in.

Today, Microsoft announced that the new XLOOKUP function for Microsoft Excel is now generally available for Office 365 users.

Which version of Excel has XLOOKUP?

As usual, the function is available for the latest Office365 version. Okay, but what happened with everybody else? Should I upgrade my Excel and Office package, or is there a compatible function?

We have splendid news for you.

Jump to the next chapter and discover how to use XLOOKUP under Excel 2010, Excel 2013, and above.

XLOOKUP compatibility using a UDF

Many Excel VBA users follow a simple rule: If the given function does not exist, create it! This UDF was built for people who use older Excel versions that do not have access to that function.

The solution provides the chance to play with the new functionality.

How to implement the new function?

We have two choices to implement the function. Insert a VBA code manually or apply a ready-to-use solution with our UDF library (free excel add-in)

Both solutions have the same output: You’ll be able to use the XLOOKUP compatible function with earlier Excel versions.

Method 1:

Steps to add an XLOOKUP compatible function to your Workbook:

  • Press Alt+F11 to open the Visual Basic Editor
  • Right-click on the ThisWorkbook and insert a new (blank) module
  • Copy the code below and paste it into the new module
  • Close the VBA editor
  • Save the Workbook as a .xlsm format

From now on, you can use the XLOOKUP function under Excel 2010 and above.

Method 2:

Install our free excel UDF library. It can take only seconds. Follow this guide on how to install a new add-in. After the successful installation, you’ll see the DataFX tab on the ribbon.

xlookup custom add-in

If you want more info about the function, locate the LOOKUP tab.

xlookup backward  compatibility function

If the add-in is installed, you can use the UDF library!

XLOOKUP compatibility – Copy the Source code

You have only to copy and paste the code below:

Option Explicit

Function DxLookup(r1 As Variant, r2 As Range, r3 As Variant, Optional arg1 As Variant, Optional arg2 As Variant) As Range 'v1.02
If IsMissing(arg1) Then arg1 = 0
If IsMissing(arg2) Then arg2 = 0
Dim rsult As Variant 'take the final result array
Dim r2width As Integer: r2width = r2.Columns.Count
Dim r3width As Integer: r3width = r3.Columns.Count
Dim rtnHeaderColumn As Boolean: rtnHeaderColumn = r2width > 1
If r2width > 1 And r2width <> r3width Then
   DxLookup = CVErr(xlErrRef)
   Exit Function
End If
Dim srchVal As Variant: srchVal = r1 'THE SEARCH VALUE
Dim sIndex As Double: sIndex = r2.Row - 1 'the absolute return range address
Dim n As Long 'for array loop
'format the search value for wildcards or not
If (arg1 <> 2 And VarType(r1) = vbString) Then srchVal = Replace(Replace(Replace(srchVal, "*", "~*"), "?", "~?"), "#", "~#") 'for wildcard switch, escape if not
Dim srchType As String
Dim matchArg As Integer
Dim lDirection As String
Dim nextSize As String
Select Case arg1 'work out the return mechanism from parameters, index match or array loop
    Case 0, 2
        If arg2 = 0 Or arg2 = 1 Then
            srchType = "im"
            matchArg = 0
        End If
    Case 1, -1
        nextSize = IIf(arg1 = -1, "s", "l") 'next smaller or larger
        If arg2 = 0 Or arg2 = 1 Then
            srchType = "lp"
            lDirection = "forward"
        End If
End Select
Select Case arg2 'get second parameter processing option
    Case -1
        srchType = "lp": lDirection = "reverse"
    Case 2
        srchType = "im": matchArg = 1
    Case -2
        srchType = "im": matchArg = -1
End Select
If srchType = "im" Then ' for index match return
    If rtnHeaderColumn Then
        Set DxLookup = r3.Columns(WorksheetFunction.Match(srchVal, r2, matchArg))
        Set DxLookup = r3.Rows(WorksheetFunction.Match(srchVal, r2, matchArg))
    End If
    Exit Function
Else  'load search range into array for loop search
    Dim vArr As Variant: vArr = IIf(rtnHeaderColumn, WorksheetFunction.Transpose(r2), r2) 'assign the lookup range to an array
    Dim nsml As Variant: ' nsmal - next smallest value
    Dim nlrg As Variant: ' nlrg - next largest value
    Dim nStart As Double: nStart = IIf(lDirection = "forward", 1, UBound(vArr))
    Dim nEnd As Double: nEnd = IIf(lDirection = "forward", UBound(vArr), 1)
    Dim nStep As Integer: nStep = IIf(lDirection = "forward", 1, -1)
        For n = nStart To nEnd Step nStep
            If vArr(n, 1) Like srchVal Then Set DxLookup = IIf(rtnHeaderColumn, r3.Columns(n), r3.Rows(n)): Exit Function 'exact match found
            If nsml < vArr(n, 1) And vArr(n, 1) < srchVal Then 'get next smallest
                Set nsml = r2.Rows(n)
            End If
            If vArr(n, 1) > srchVal And (IsEmpty(nlrg) Or nlrg > vArr(n, 1)) Then 'get next largest
                Set nlrg = IIf(rtnHeaderColumn, r2.Columns(n), r2.Rows(n))
            End If
End If
If arg1 = -1 Then 'next smallest
    Set DxLookup = r3.Rows(nsml.Row - sIndex)
ElseIf arg1 = 1 Then 'next largest
    Set DxLookup = r3.Rows(nlrg.Row - sIndex)
End If
End Function

Learn more…