Excel Pricing Model with value bands

Question

I am working on developing a pricing model in Excel and am running into an error when trying to use an array formula for the below.

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% |

The variables i am searching for in the formula are Region 1 Cycle 2 value 35000

I need the formula to look for the Region Code, the value between the Low and High figures, and then finally which cycle the value is in to return the % in the table on the right side under the Cycle Columns.

So far I have tried an Index Match Array to search for these variables:

=INDEX(I12:L15, MATCH(1,(C:C=P19)*(10:10=Q19)*((D:D>=R19)/(E:E<=R19)),0))

P19= 1 (region code), Q19=2, R19= 35000 . Column C in this case is the Region Code Column of the table, Column D is the Low Value, Column E is the High value, and Row 10 is where the Cycles are located.

Excel is running out of resources when trying to calculate this. I'm sure there is a better way to do this calculation, perhaps with multiple Vlookup/Hlookup/Lookups together.

I could also set up the table in a different manner I suppose, but I felt this was the best way to present the data.

Answer 1

Good question for first post IMHO.

I think the formula below is on the right lines and shows how you can do it more efficiently using INDEX/MATCH, but lacks error handing at present (i.e. value less than 10000 would give #N/A, greater than 125000 would give the same answer as 125000).

=INDEX($I:$L,MATCH(R2,INDEX($D:$D,MATCH(P2,$C:$C,0)):INDEX($D:$D,MATCH(P2,$C:$C)))+MATCH(P2,$C:$C,0)-1,MATCH(Q2,$I$1:$L$1,0))

EDIT

Here is a revised formula with error handling

=IFERROR(IF($R2>INDEX(E:E,MATCH($P2,$C:$C)),"",INDEX($I:$L,MATCH($R2,INDEX($D:$D,MATCH($P2,$C:$C,0)):INDEX($D:$D,MATCH($P2,$C:$C)))+MATCH($P2,$C:$C,0)-1,MATCH($Q2,$I$1:$L$1,0))),"")

Answer 2

Here is a UDF which you can call from in the sheet. The code would go in a standard module. Some more error checking is probably needed.

If value not found -999999 is returned which is then formatted as a percentage in the sheet.

In sheet use:

Code:

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