I'm trying to write a custom XLOOKUP function that will allow me to specify adjustments to the original lookup value if a match isn't found

I hate having nested lookups as deep as mine are so I thought it would be a simple task to add an optional modifier parameter that, if utilized, would tell the function to adjust the lookup value and try to look again. For example =VariableXLOOKUP(A1,B;B,C:C,-0.01) ould look for A1 like normal but if no match is found, it would look for A1-0.01. I feel like I have tried every way I can think of to write this and while I can replicate the normal xlookup function, I cannot get it to show results that require the use of the modifier. Ideally, I'd like to be able to list as many modifiers in the function as I want but I am fine capping it at less than 10 as well.

The first version I tried that would not show results that required the modifier:

Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, return_range As Range, modifier As Variant) As Variant
    Dim result As Variant

    ' Try the initial lookup
    result = Application.WorksheetFunction.XLookup(lookup_value, lookup_range, return_range)

    ' If no match and modifier is specified, try again with modified criteria
    If IsError(result) Then
        result = Application.WorksheetFunction.XLookup(lookup_value + modifier, lookup_range, return_range)
    End If

    VariableXLOOKUP = result
End Function

Then I tried this version with the same results:

Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, return_range As Range, ParamArray modifiers() As Variant) As Variant
    Dim result As Variant
    Dim i As Long

    ' Try the initial lookup
    result = Application.WorksheetFunction.XLookup(lookup_value, lookup_range, return_range)

    ' If no match and modifiers are specified, try again with each modifier
    If IsError(result) And Not IsMissing(modifiers) Then
        For i = LBound(modifiers) To UBound(modifiers)
            result = Application.WorksheetFunction.XLookup(lookup_value + modifiers(i), lookup_range, return_range)
            If Not IsError(result) Then Exit For ' Exit loop if a match is found
        Next i
    End If

    VariableXLOOKUP = result
End Function

I had also tried a version that used Index and Match but in all iterations of this or the xlookup version, it seems to never return results from the loop part of the function.
I have modified the code so the initial lookup is result = Application.WorksheetFunction.XLookup(lookup_value + modifier, lookup_range, return_range) and it does return the results I expect (matches for lookup_value + modifier) so the issue is with error handling and moving on to the second lookup attempt as all lookup variations that I've typed work if they are the initial lookup in the function but fail as soon as a second look is required.

**Edited for grammar and spelling

This works for me:

Sub tester()
    Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10])
    Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10], -0.1)
    Debug.Print VariableXLOOKUP(6, [A1:A10], [B1:B10], -0.1, 0.1, 0.2)
End Sub

Function VariableXLOOKUP(lookup_value As Variant, lookup_range As Range, _
                         return_range As Range, ParamArray modifiers() As Variant) As Variant
    Dim result As Variant, x As Long, modVal

    result = Application.XLookup(lookup_value, lookup_range, return_range)

    If IsError(result) Then
        For x = LBound(modifiers) To UBound(modifiers)
            modVal = modifiers(x)
            Debug.Print "Using modifier: " & modVal
            result = Application.XLookup(lookup_value + modVal, _
                                                lookup_range, return_range)
            If Not IsError(result) Then Exit For 'got a match...
        Next x
    End If
    VariableXLOOKUP = IIf(IsError(result), "No match", result)
End Function

Dropping the WorksheetFunction allows the lookup to return an error value.

Using a paramarray allows you to pass in one or more modifiers to try if the original value isn't found. See: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-parameter-arrays