Derive cell value of an Excel Table based on two parameters

I have 2 columns in excel, A and B. In A I have percentages (rates) and in B integers numbers (years).

 rating PD  year
    0.39%   3
    0.88%   2
    1.32%   17
    0.88%   1
    0.26%   15
    0.17%   2
    0.17%   2
    0.59%   2
    0.59%   2

Then I have a Table in which in column F I have years and in row I have text.

Like this (the table is much bigger and years go up to 30):

    Rating          
Year AAA     AA+      AA      AA-
1   0.003%  0.008%  0.018%  0.049%
2   0.016%  0.037%  0.074%  0.140%
3   0.041%  0.091%  0.172%  0.277%
4   0.085%  0.176%  0.318%  0.465%
5   0.150%  0.296%  0.514%  0.708%

And so on (the table is much bigger than this).

So I would need a function, or a shortcut, which, for a given rate in column A and a given year in column B, gives me, in column C, the corresponding rating (AAA,AA+,AA etc.).

In the table the rates are the maximum. So if I have A1=0.50% and B1=2, then I go to look at the table, year 2 and corresponding rate, which is 0.74% (and therefore AA), because AA+ is 0.37% and is too low.

In other words, AA+ and year 2 are all the rates between 0.16% and 0.37%. And AA with year 2 are all the rates between 0.37% and 0.74%.

Do you know how I could perform this task?

Thank you very much.

Solution

For the sake of code readability, I've used two custom-made functions, alongside the main procedure shown here. Otherwise it would be a huge code-dump.

Before you begin, you have to change/check these data fields.

The (blue) data table needs to be named "scores" (or changed inside code to your own name)
Same goes for the (green) grades table - to be named "grades" and start in F1
Last but not least, the code presumes these two tables are in a sheet called "Sheet1"

So all of this needs to be changed within the code, if the names do not match!

Now to the procedure:

Option Explicit
Private Sub run_through_scores()

    Dim scores As ListObject ' table from A1
    Dim grades As ListObject ' table from F1
    Set scores = Sheets("Sheet1").ListObjects("scores")
    Set grades = Sheets("Sheet1").ListObjects("grades")

    Dim cell As Range ' for "for" loop
    Dim inrow As Long ' will store in which row the year is
    Dim resultColumn As Integer ' will store in which column the percentage is

    'for every cell in second column of scores table (except header)
    For Each cell In scores.ListColumns(2).DataBodyRange
        inrow = get_year(cell).Row - 1
        ' ^ returns Row where result was found, -1 to accoutn for header

        'using our get_interval() function, _
         determines in which column is the sought percentage
        resultColumn = get_interval(cell.Offset(0, -1), inrow).Column
        cell.Offset(0, 1) = Sheets("Sheet1").Cells(1, resultColumn) 
        'write result in Column C   ^
    Next cell

End Sub

And to the functions:

`get_year()`

returns a Range Object from the "grades" table, in which we found the matching year from our "scores" table. If the desired year is not found, it returns the year closest to it (the last table row)

' Returns a Range (coordinates) for where to search in second table
Private Function get_year(ByVal year As Variant) As Range

    Dim grades As ListObject ' table from F1
    Set grades = Sheets("Sheet1").ListObjects("grades")

    Dim testcell As Range
    Set testcell = grades.ListColumns(1).DataBodyRange.Find(year, LookIn:=xlValues)

    'if found
    If Not testcell Is Nothing Then
        Set get_year = testcell
    Else
        Dim tbl_last_row As Long 'if year not found, return last row
        tbl_last_row = grades.ListColumns(1).DataBodyRange.Rows.Count
        Set get_year = grades.ListColumns(1).Range(tbl_last_row)
    End If

End Function

And the second function:

`get_interval()`

returns a Range Object from the "grades" table. It compares individual cell ranges and returns upon a) if the sought percent from "scores" is less or equal (<=) then current cell percent or b) if we went through all the cells, it returns the last cell (because it must be higher, than the maximum of specified interval)

Private Function get_interval(ByVal what As Variant, ByVal inyear As Long) As Range

    Dim grades As ListObject ' table from F1
    Set grades = Sheets("Sheet1").ListObjects("grades")

    Dim cell As Range
    For Each cell In grades.ListRows(inyear).Range

    'check for interval 
        If what <= cell And cell.Column <> 6 Then 'we don't want to check year column
            Set get_interval = cell
            Exit Function
        End If
    Next cell

    ' if we arrived here, at this stage the result will always be the last cell
    Set get_interval = grades.ListRows(inyear).Range(, grades.ListColumns.Count)

End Function

Upon firing (invoking) the run_through_scores() procedure, we get the results as expected:

if you have any questions, please let me know :)