Excel多列混合匹配查找：按产品及订购量匹配对应单价

公式方案（推荐，无需VBA） #

前提条件 #

• 已将合并居中的产品名称改为每行重复
• 同一产品的订购量门槛列需按从小到大升序排列

普通区域公式（适用于非结构化表格） #

如果数据位于A:C列（A=产品，B=订购量门槛，C=单价），客户订购产品在E2，订购数量在F2，使用以下公式：

=XLOOKUP(1,(A:A=E2)*(B:B<=F2),C:C,"无匹配",0,-1)

或使用INDEX+MATCH组合（旧版Excel需按Ctrl+Shift+Enter执行数组运算）：

=INDEX(C:C,MATCH(1,(A:A=E2)*(B:B<=F2),1))

结构化表格公式（更灵活，适配新增数据） #

将数据区域转为Excel结构化表格（选中数据→按Ctrl+T），假设表格命名为PriceTable，公式可写为：

=XLOOKUP(1,(PriceTable[产品]=E2)*(PriceTable[订购量门槛]<=F2),PriceTable[单价],"无匹配",0,-1)

结构化表格会自动扩展范围，新增产品或折扣档位时无需修改公式。

VBA自定义函数方案 #

若公式无法满足需求，可使用以下VBA自定义函数，支持动态识别新增数据：

1. 打开Excel，按Alt+F11进入VBA编辑器
2. 插入新模块（右键工作簿→插入→模块）
3. 粘贴以下代码：

Function GetUnitPrice(productName As String, orderQty As Double, dataRange As Range) As Variant
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim maxThreshold As Double
    Dim matchedPrice As Variant
    
    Set ws = dataRange.Worksheet
    lastRow = ws.Cells(ws.Rows.Count, dataRange.Column).End(xlUp).Row
    
    maxThreshold = -1
    matchedPrice = "无匹配"
    
    ' 遍历数据，找到对应产品下符合条件的最高门槛单价
    For i = dataRange.Row To lastRow
        If ws.Cells(i, dataRange.Column).Value = productName Then
            If ws.Cells(i, dataRange.Column + 1).Value <= orderQty And ws.Cells(i, dataRange.Column + 1).Value > maxThreshold Then
                maxThreshold = ws.Cells(i, dataRange.Column + 1).Value
                matchedPrice = ws.Cells(i, dataRange.Column + 2).Value
            End If
        End If
    Next i
    
    GetUnitPrice = matchedPrice
End Function

4. 返回Excel，在目标单元格输入公式使用：

=GetUnitPrice(E2,F2,A:C)

参数说明：

• E2：客户订购的产品名称
• F2：客户订购的数量
• A:C：产品-档位-单价的数据区域

常见问题说明 #

之前使用Index-Match多条件方案返回异常，大概率是以下原因：

• 未设置正确的MATCH匹配类型（需设为1，对应升序数据的近似匹配）
• 同一产品的订购量门槛未按升序排列
• 未正确执行数组运算（旧版Excel需按Ctrl+Shift+Enter）

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