Excel VBA function to lookup value in a Cell range

Question

I know how to do this in excel using match and index. My spreadsheet has a lot of these lookups though and it has to be easily auditable.

In pure excel formulas it is a simple:

=index(match()...match()) of the table

although easy to do it created big headackes if the size of the table changes,etc. the readability of the formula is also bad. Named ranges for the table and the headers and columns make it even more difficult to troubleshoot.

      Col1  Col2    Col3
Row1    1   2   3
Row2    4   5   6
Row3    7   8   9

I am naming this range with the row and column names as first row and column respectively.

so the Excel variable will be called test_table.

want to write a VBA function that i can call with:

=VBAlookup("Row2", "Col2", test_table)
returns 5

at moment i am using python with DataNitro and pandas to easily do this splicing. Not good at VBA so writing this in VBA going to take quite a while. Sure that I am not the first or only to look for this functionality, but google search does not seem to get me anywhere.

Don't want to answer my own question, but my solution so far (adapted from @John Bustos' answer below) is:

Public Function VBAlookup(RowName As Variant, ColName As Variant, Table As Range) As Variant

Dim RowNum As Integer
Dim ColNum As Integer

    VBAlookup = "Not Found"

    For RowNum = 1 To Table.Rows.Count
        If Table.Cells(RowNum, 1) = RowName Then
            For ColNum = 1 To Table.Columns.Count
                If Table.Cells(1, ColNum) = ColName Then
                    VBAlookup = Table.Cells(RowNum, ColNum)
                End If
            Next ColNum
        End If
    Next RowNum

End Function

This gets any type of content in correct format and also gives some feedback if value is not in the table

Answer 1

This should do it:

Public Function VBAlookup(RowName As String, ColName As String, Table As Range) As String

Dim RowNum As Integer
Dim ColNum As Integer

    VBAlookup = ""

    For RowNum = 1 To Table.Rows.Count
        If Table.Cells(RowNum, 1).Text = RowName Then
            For ColNum = 1 To Table.Columns.Count
                If Table.Cells(1, ColNum).Text = ColName Then
                    VBAlookup = Table.Cells(RowNum, ColNum).Text
                End If
            Next ColNum
        End If
    Next RowNum

End Function

Note that I did not put in any error checking, etc, which would be good practice!