VBA自定义函数中VLOOKUP匹配异常及数字与字符串统一匹配问题的解决方案咨询

我来帮你一步步解决这个问题，先拆解下你遇到的两个核心问题，再给出优化后的方案：

第一个问题：为什么移除.WorksheetFunction才解决#VALUE!错误？ #

当你使用Application.WorksheetFunction.VLookup时，如果查找不到目标值，它会直接抛出运行时错误，而VBA自定义函数无法捕获这种错误，直接返回#VALUE!。而去掉.WorksheetFunction后，Application.VLookup在找不到值时会返回一个错误值（比如#N/A），这时候IsError就能正常捕获，从而返回你设定的"PLEASE CHECK"。

第二个问题：数字与字符串不匹配的解决方法 #

你尝试用.Text来转字符串的思路是对的，但Range对象没有.Text属性（只有单个单元格有），所以Sheet3.Range("B:B").Text是无效的。我们需要把查找值和目标范围的所有值统一转成字符串再匹配，这里给你两种高效的实现方式：

方式1：用Application.Match结合数组转字符串 #

把目标范围的值转成字符串数组，再用Match查找转成字符串的输入值：

Function reviewFun(inputCell As Range, scopeCell As Range)
    Dim targetRange As Range
    Dim lookupValue As String
    Dim matchResult As Variant
    
    ' 统一转成字符串
    lookupValue = CStr(inputCell.Value)
    
    ' 根据scope确定目标工作表和范围（建议用实际数据范围，而非整列，提升效率）
    If scopeCell.Value = "AAA" Then
        Set targetRange = Sheet3.Range("B1:B" & Sheet3.Cells(Sheet3.Rows.Count, "B").End(xlUp).Row)
    Else
        Set targetRange = Sheet4.Range("B1:B" & Sheet4.Cells(Sheet4.Rows.Count, "B").End(xlUp).Row)
    End If
    
    ' 把目标范围转成字符串数组，再执行匹配
    matchResult = Application.Match(lookupValue, Application.ConvertToText(targetRange), 0)
    
    ' 判断匹配结果
    If IsError(matchResult) Then
        reviewFun = "PLEASE CHECK"
    Else
        ' 根据scope返回对应提示
        reviewFun = IIf(scopeCell.Value = "AAA", "FINE", "ALSO FINE")
    End If
End Function

方式2：用Range.Find方法（更灵活的文本匹配） #

Find方法可以直接指定按文本匹配，避免数字和字符串类型不匹配的问题：

Function reviewFun(inputCell As Range, scopeCell As Range)
    Dim targetSheet As Worksheet
    Dim targetRange As Range
    Dim foundCell As Range
    Dim lookupValue As String
    
    lookupValue = CStr(inputCell.Value)
    
    ' 确定目标工作表
    If scopeCell.Value = "AAA" Then
        Set targetSheet = Sheet3
    Else
        Set targetSheet = Sheet4
    End If
    Set targetRange = targetSheet.Range("B1:B" & targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Row)
    
    ' 按文本精确匹配
    Set foundCell = targetRange.Find( _
        What:=lookupValue, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False)
    
    ' 判断是否找到
    If foundCell Is Nothing Then
        reviewFun = "PLEASE CHECK"
    Else
        reviewFun = IIf(scopeCell.Value = "AAA", "FINE", "ALSO FINE")
    End If
End Function

关于是否可以不用VLOOKUP？ #

当然可以！上面两种方案都没有用VLOOKUP：

• Match是专门用于查找位置的函数，比VLOOKUP更轻量（不需要指定返回列）
• Range.Find是VBA原生的查找方法，功能更灵活，支持更多匹配规则

额外优化建议 #

• 避免直接用整列B:B查找，这会遍历大量空白单元格，降低效率，建议用End(xlUp)获取实际数据的最后一行
• 把参数名从input和scope改成inputCell和scopeCell，避免和VBA关键字冲突（虽然input不是关键字，但命名更清晰）

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