我正在开发Excel中的定价模型，并且在尝试使用以下数组公式时遇到错误。
Pricing Table
| | | | | | | | Cycle
|-----------|-------------|--------|--------|------|------|------|------|------|------|------|
| Region | Region Code | Low | High | A | X | P | 1 | 2 | 3 | 4 |
| NorthEast | 1 | 10000 | 25000 | -61% | N/A | 38% | TBD | | | |
| NorthEast | 1 | 25000 | 50000 | -32% | N/A | -2% | -2% | -2% | -2% | -2% |
| NorthEast | 1 | 50000 | 75000 | -21% | -50% | -34% | -34% | -34% | -34% | -34% |
| NorthEast | 1 | 75000 | 100000 | -38% | -26% | -19% | -19% | -19% | -19% | -19% |
| NorthEast | 1 | 100000 | 125000 | -27% | -45% | -21% | -21% | -21% | -21% | -21% |
我在公式中搜索的变量是
1区
周期2
价值35000
我需要公式来查找“区域代码”，“低”和“高”数字之间的值，然后最终找到该值所在的循环以返回“循环”列下右侧表中的％。
到目前为止，我已经尝试过使用索引匹配数组来搜索以下变量：
=INDEX(I12:L15, MATCH(1,(C:C=P19)*(10:10=Q19)*((D:D>=R19)/(E:E<=R19)),0))
P19 = 1（区域代码），Q19 = 2，R19 = 35000。在这种情况下，列C是表的“区域代码”列，列D是低值，列E是高值，行10是循环所在的位置。
尝试计算此值时，Excel资源不足。我敢肯定，有更好的方法可以执行此计算，也许可以同时使用多个Vlookup / Hlookup / Lookups。
我还可以采用其他方式设置表，但我认为这是呈现数据的最佳方法。
最佳答案
这是您可以在工作表中调用的UDF。该代码将放入标准模块中。可能需要更多错误检查。
如果找不到值，则返回-999999，然后将其格式化为工作表中的百分比。
在工作表中使用：
码：
Option Explicit
Public Function GetPercentage(ByVal Region As Long, ByVal Cycle As Long, ByVal Amount As Double) As Double
Dim wb As Workbook
Dim wsSource As Worksheet
Set wb = ThisWorkbook
Set wsSource = wb.Worksheets("Sheet1") 'change as appropriate
Dim lookupSource()
Dim lastRow As Long
If Cycle < 1 Or Cycle > 4 Then
MsgBox "Invalid cycle chosen"
GetPercentage = -999999 'chose your not found return value
Exit Function
End If
With wsSource
lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
'Assuming data starts in C2
lookupSource = wsSource.Range("C2:N" & lastRow).Value2 'change as appropriate
Dim requiredColumn As Long
requiredColumn = Application.Match(Cycle, wsSource.Range("C2:N2"), 0)
Dim currentRow As Long
For currentRow = 2 To UBound(lookupSource, 1)
If lookupSource(currentRow, 2) = Region And lookupSource(currentRow, 3) <= Amount And lookupSource(currentRow, 4) >= Amount Then
GetPercentage = lookupSource(currentRow, requiredColumn)
Exit Function
Else
GetPercentage = -999999
End If
Next currentRow
End Function