Using VLOOKUP with multiple criteria/multidimensional Vlookup

Question

I am trying to create an amortisation table where the interest rate depends on two inputs provided by the user.

X represents rows and Y represents columns. Values of X, Y and interest rates are already set in a 4 X 6 table. For example if the user inputs X=2 and Y=3, then interest rate will be determined to be 5%.

The IF function could work but it would take up a lot of time and is not efficient.

I considered using an array, and I think a Vlookup will be the most efficient. In Excel I used Vlookup along with Match and it worked but I am having difficulty translating it to VBA code.

Option Explicit

Sub Amortisation()

    Dim intRate, loanLife, initLoanAmt
    Dim yrBegBal, intComp, prinComp, yrEndBal, annualPmt
    Dim outRow, rowNum, outsheet

    outRow = 3 'output table begins from row 4

    outsheet = "loan amort"
    Worksheets(outsheet).ActivateDo

    loanLife = InputBox("Enter loan life." _
      & " Loan life must be a whole number")

    If loanLife < 0 Or (loanLife - Round(loanLife) <> 0) Then
        MsgBox ("Loan life must be a whole number.")
        End
    End If

    initLoanAmt = InputBox("Enter loan amount." _
      & " Loan amount must be a positive whole number")

    If initLoanAmt < 0 Or (initLoanAmt - Round(initLoanAmt) <> 0) Then
        MsgBox ("Loan amount must be a positive whole number.")
        End
    End If

End Sub

Rather than prompting for Interest rates as I have done with the other inputs, I want VBA to use the inputs given to choose an interest rate from the table below.

So if X (loan life) is 5 and Y (initloanamount) is 700, then I want VBA to use 10 as the rate.

After this I can then continue with the amortisation table using PMT function.

Answer 1

Just make your table a named range called "InterestRates". Then you can access the named range in VBA as such:

Option Explicit

Sub Amortisation()

Dim intRate As Double, loanLife As Long, initLoanAmt As Long
Dim yrBegBal, intComp, prinComp, yrEndBal, annualPmt
Dim outRow, rowNum
Dim outsheet As Worksheet
Dim rng As Range

outRow = 3 'output table begins from row 4

Set outsheet = Worksheets("loan amort")
outsheet.ActivateDo

loanLife = InputBox("Enter loan life." _
  & " Loan life must be a whole number")

If loanLife < 0 Or (loanLife - Round(loanLife) <> 0) Then
    MsgBox ("Loan life must be a whole number.")
End If

initLoanAmt = InputBox("Enter loan amount." _
  & " Loan amount must be a positive whole number")

If initLoanAmt < 0 Or (initLoanAmt - Round(initLoanAmt) _
  <> 0) Then
    MsgBox ("Loan amount must be a positive whole number.")
End If

Set rng = outsheet.Range("InterestRates")

loanLife = Evaluate("MATCH(" & loanLife & ",INDEX(InterestRates,,1),0)")
initLoanAmt = Evaluate("MATCH(" & initLoanAmt & ",INDEX(InterestRates,1,),0)")

intRate = rng.Cells(loanLife, initLoanAmt).Value

End Sub

This should work for you assuming you have named your entire table (including the headers and row names) "InterestRates". This also assumes that your InterestRates range is on your "loan amort" sheet. The desired interest rate will be assigned to the variable intRate and you can add code at the end to use it however you like.

By the way, I declared some of your variables with the variable types. You should consider adding the variable types to the rest. I also removed the two End lines as they were not necessary.