VBA中HLookup函数返回错误结果的问题求助

核心问题 #

你用HLOOKUP时犯了两个关键错误：

1. HLOOKUP查找逻辑搞反：HLOOKUP要求查找值必须在查找区域的第一行，但你要找的最大值在LookupVector的第二行（occData所在行），第一行（Figs行）根本不存在这个最大值。
2. 默认近似匹配导致错误结果：HLOOKUP省略第4参数时默认用近似匹配（TRUE），当找不到目标值时，会返回第一行中小于等于查找值的最后一项，也就是首行最后一个单元格。

另外代码里Set ResultVector =1是语法错误，Set用于对象赋值，直接写ResultVector =1即可，但这不是核心问题。

修正方案 #

推荐用INDEX+MATCH组合，逻辑更直观，适配“找数值行的最大值，返回对应首行关联值”的需求：

替换原来的HLOOKUP代码段：

' 找到最大值在occData区域中的位置
Dim maxPos As Long
maxPos = WorksheetFunction.Match(LookupCellMax.Value, occData, 0)
' 提取首行对应位置的关联值
cFreq.Offset(2, nums + 6).Value = WorksheetFunction.Index(Figs.Resize(1, nums), maxPos)

如果一定要用HLOOKUP，需要调换查找区域的行顺序，同时开启精确匹配：

' 重新构造查找区域：第一行是数值行（occData），第二行是关联值行（Figs）
Dim correctLookupRange As Range
Set correctLookupRange = Range(occData, Figs.Resize(1, nums)).Resize(2, nums)
' 用HLOOKUP精确匹配查找最大值，返回第二行的关联值
cFreq.Offset(2, nums + 6).Value = WorksheetFunction.HLookup(LookupCellMax.Value, correctLookupRange, 2, False)

额外优化建议 #

1. 取消Worksheets("feldolg").Activate，改用With语句绑定工作表，避免激活其他工作表导致错误：

With Worksheets("feldolg")
    Set tart = .Range("B2:H1222")
    Set Figs = .Cells(1, 11)
    Set cFreq = .Cells(2, 11)
    ' 其他Range对象前都加.前缀
End With

2. 取消nums的注释，声明变量：Dim nums As Byte，避免未声明变量引发的潜在问题。

内容的提问来源于stack exchange，提问作者A Pofta Tapofta