Get cell address from custom INDEX MATCH function-CodePudding

I'm using this handy custom function

Public Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.IfError(Application.Index(rCol, Application.Match(lk, lCol, 0)), "0")

End Function

Is it possible to get cell address of "X" to replace its value, where

X = XLOOKUP("text", Sheet1.Range("D1:D300"), Sheet1.Range("E1:E300"))

Edit: Thanks to Scott ...

x = XLOOKUP("text", Sheet1.Range("D1:D300"), Sheet1.Range("E1:E300"))
xNew = Sheet1.Range("E1:E300").Cells(Application.Match("text", Sheet1.Range("D1:D300"), 0)).Address(0, 0)
If x = "" Then Sheet1.Range(xNew) = 0

CodePudding user response：

Change your function to return a range and then use that in the sub:

Public Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range) As Range

    Dim mtch As Variant
    mtch = Application.Match(lk, lCol, 0)
    
    If IsError(mtch) Then Exit Function
    
    Set XLOOKUP = rCol.Cells(mtch, 1)

End Function

Sub doThings()
    Dim x As Range
    Set x = XLOOKUP("text", Sheet1.Range("D1:D300"), Sheet1.Range("E1:E300"))
    If Not x Is Nothing Then
        If x.Value = "" Then x = 0
        MsgBox x.value
    Else
        MsgBox "text not found"
    End If
End Sub