match 3 criteria in excel

[Venkateshwara Iyer]

SALES 65000 103000 161000

CRITERIA 1 50000-99999 100000-149999 150000-199999

CRETERIA 2 12 16 24

CRETERIA 3 3% 3.5% 4%



REQUIRED YOUR HELP PLEASE
EXAMPLE 1

IF THE SALE IS DONE MORE THAN 151000 ( CRITERIA 1 ) BUT HAVE DONE ONLY 12 ( CRITERIA 2 ) THEN THEY ARE ELIGIBLE ONLY FOR 3% AND NOT 4%

EXAMPLE 1

IF THE SALE IS DONE MORE THAN 50000 ( CRITERIA 1 ) BUT HAVE DONE ONLY 24 ( CRITERIA 2 ) THEN THEY ARE ELIGIBLE ONLY FOR 3% AND NOT 4% ONCE AGAIN.

PLEASE LET ME KNOW HOW TO SOLVE THIS PLEASE

I USED THIS FORMULA ONLY FOR CRITERIA 1 BUT NOT ABLE TO KNOW HOW TO MATCH THIS WITH CRITERIA 2 AND 3
If(A1>=400000,A1*4%,If(A1>=399999,A1*4%,If(A1>=299999,A1*4%,If(A1>=249999,A1*4%,If(A1>=199999,A1*4%,If(A1>=149999,A1*4%,If(A1>=99999,A1*3.5%,If(A1>=50000,A1*3.5%,A1*3%))))

 

[PCL]

A UDF coulf help
Assuming inputs in "A10" and "B10" for sales and Quantity
Somewhere put =Ratio(A10;B10)

Option Base 1
Function Ratio(ByVal Crit1 As Single, ByVal Crit2 As Single)
Dim Sales
    Sales = Array(50000, 100000, 150000)
Dim Qty
    Qty = Array(12, 16, 24)
Dim Result
    Result = Array(0.03, 0.035, 0.04)
Dim I As Integer, II  As Integer
    
    For I = 1 To UBound(Sales, 1)
        If (Crit1 < Sales(I)) Then Exit For
    Next I
    For II = 1 To UBound(Qty, 1)
        If (Crit2 < Qty(II)) Then Exit For
    Next II
    If (I = 1) Then Ratio = "Error"
    If (II = 1) Then Ratio = "Error"
    I = IIf(II < I, II, I)
    Ratio = Result(I - 1)
End Function