LibreOffice Calc使用VLOOKUP时如何获取匹配结果的单元格地址

原来的VLOOKUP函数本身返回的是匹配单元格的值而非单元格引用，所以直接套CELL()、ADDRESS()时找不到对应的引用对象，自然会返回#REF!错误。

1. 工作表公式实现（无需宏） #

用MATCH+INDEX组合替代VLOOKUP，示例公式如下，可直接返回匹配单元格地址：

=CELL("address",INDEX(J2:J7,MATCH(B11,G2:G7,0)))

如果需要返回行列号格式的结果，直接调用ROW()、COLUMN()即可：

=ROW(INDEX(J2:J7,MATCH(B11,G2:G7,0))) & "," & COLUMN(INDEX(J2:J7,MATCH(B11,G2:G7,0)))

2. 宏封装实现（适配你现有代码逻辑） #

你可以直接基于现有代码的局部变量，修改为返回匹配单元格行列号的函数，示例代码如下：

' 函数返回值为长整型数组，下标0为行号（从1开始计数），下标1为列号（从1开始计数）
' 匹配失败则返回空数组
Function getLookupPosition(valColumn as Integer) As Variant
    oDoc = ThisComponent
    oSheet = oDoc.Sheets(workSheet)
    ' 解析查找范围的起始、结束行列坐标
    Dim oRange As Object
    oRange = oSheet.getCellRangeByName(lookupTopLeft & ":" & lookupBottomRight)
    Dim startCol As Long, startRow As Long, endCol As Long, endRow As Long
    startCol = oRange.RangeAddress.StartColumn
    startRow = oRange.RangeAddress.StartRow
    endCol = oRange.RangeAddress.EndColumn
    endRow = oRange.RangeAddress.EndRow
    
    ' 获取查找值
    Dim oCell As Object, searchValue As String
    oCell = oSheet.GetCellByPosition(dataCellColumn, dataCellRow)
    searchValue = oCell.getString()
    
    ' 遍历查找范围第一列匹配值
    Dim i As Long
    For i = startRow To endRow
        Dim currentCell As Object
        currentCell = oSheet.getCellByPosition(startCol, i)
        If currentCell.getString() = searchValue Then
            ' 匹配成功，计算目标单元格的行列（LibreOffice内部行列从0开始，返回值统一转1开始计数更符合使用习惯）
            Dim res(1) As Long
            res(0) = i + 1
            res(1) = startCol + valColumn
            getLookupPosition = res
            Exit Function
        End If
    Next i
    
    ' 匹配失败返回空数组
    getLookupPosition = Array()
End Function

' 如果需要直接返回地址字符串，可以用以下封装
Function getLookupAddress(valColumn as Integer) As String
    Dim pos As Variant
    pos = getLookupPosition(valColumn)
    If UBound(pos) < 0 Then
        getLookupAddress = "#N/A"
        Exit Function
    End If
    ' 行列号转地址字符串
    oDoc = ThisComponent
    oSheet = oDoc.Sheets(workSheet)
    Dim oCell As Object
    oCell = oSheet.getCellByPosition(pos(1)-1, pos(0)-1)
    getLookupAddress = oCell.CellAddress.Column ' 仅返回单元格地址如J5，需要带工作表名则替换为oCell.AbsoluteName
End Function

代码说明 #

• 完全兼容你原来定义的lookupTopLeft、lookupBottomRight、dataCellColumn、dataCellRow等私有变量，无需修改原有配置
• 提供两种返回值版本：getLookupPosition返回行列号数组，可直接在其他宏中调用读取；getLookupAddress返回标准单元格地址字符串
• 增加了匹配失败的异常处理，避免运行时报错

内容的提问来源于stack exchange，提问作者Tango