LibreOffice Calc: Can I get the cell address from VLOOKUP?

Question

I'm using VLOOKUP, in Calc, like this:

VLOOKUP(B11,G2:J7,4,0)

Normally when any of us uses this, we want to get the value in the cell this function finds. In this case, rather than the value, I'd like to get a string with the cell address in it instead or the row and column of that cell. For instance, if I have a double precision floating point value of 30.14 in cell J5 and that's the answer, rather than having it return 30.14, I want it to return something like "J5" or 9,4 or some other way for me to read the result in a macro.

I've tried using =ADDRESS() and =CELL("address", ) but I'm getting errors (=CELL() gives me '#REF!').

---

EDIT: I'm using this routine as a wrapper around VLOOKUP with a table of floating point numbers (which is why it returns a DOUBLE instead of getting the cell value as a STRING or something else). All I have to do is pass it the column I want to get the data from:

Function getLookup(valColumn as Integer) as Double
    oDoc = ThisComponent
    oSheet = oDoc.Sheets (workSheet)
    rangeInfo = lookupTopLeft + ":" + lookupBottomRight
    cellRange = oSheet.getCellRangeByName(rangeInfo)
    oCell = oSheet.GetCellByPosition(dataCellColumn, dataCellRow)
    searchValue = oCell.getString()
    Mode = 0
    svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
    args = Array(searchValue, cellRange, valColumn, Mode)
    getLookup = svc.callFunction("VLOOKUP", args)
End Function

Note I'm using some local variables in this. They're private, for the module only, so I don't have to change cell references in multiple places while I'm working on designing my spreadsheet. "lookupTopLeft" and "lookupBottomRight" are "G2" and "J7", the top left and bottom right cells for the data I'm working with. "dataCellColumn", and "dataCellRow" are the column and row coordinates for the source for the key I'm using in VLOOKUP.

(@JohnSUN, I think this may be modified from an answer you provided somewhere.)

I'd like to be able to do a similar wrapper routine that would return the column and row of a cell instead of the value in the cell.

Answer 1

One of many possible options:

Option Explicit 

Const lookupTopLeft = "G2" 
Const lookupBottomRight = "J7"
Const dataCellColumn = 1
Const dataCellRow = 10
Const workSheet = 0

Function getCellByLookup(valColumn As Integer) As Variant
Dim oSheet As Variant, cellRange As Variant, oCell As Variant
Dim oColumnToSearch As Variant
Dim oSearchDescriptor As Variant
Dim searchValue As String 
Dim nRow As Long
    oSheet = ThisComponent.getSheets().getByIndex(workSheet)
    cellRange = oSheet.getCellRangeByName(lookupTopLeft + ":" + lookupBottomRight)
    searchValue = oSheet.GetCellByPosition(dataCellColumn, dataCellRow).getString()
    
Rem If we are looking not for a value, but for a cell, 
Rem then using VLOOKUP is unnecessary, a simple Find is enough  
    oColumnToSearch = cellRange.getCellRangeByPosition(0, 0, 0, _
        cellRange.getRows().getCount()-1) ' Resize full range to one first column
Rem Set search params
    oSearchDescriptor = oColumnToSearch.createSearchDescriptor()
    oSearchDescriptor.setSearchString(searchValue)
    oSearchDescriptor.SearchType = 1 ' Search in Values!
Rem Try to find searchValue in oColumnToSearch
    oCell = oColumnToSearch.findFirst(oSearchDescriptor)
    If Not IsNull(oCell) Then ' Only if the value was found
        nRow = oCell.getRangeAddress().StartRow
Rem Offset oCell to valColumn
        oCell = cellRange.getColumns().getByIndex(valColumn-1).GetCellByPosition(0,nRow)
        getCellByLookup = Replace(oCell.AbsoluteName, "$", "")
    Else    ' If the value from B11 is not found - warn about it
        getCellByLookup = "Not found"
    EndIf
End Function