Excel VBA function to search a value in a range of cells

Question

I know how to do this in Excel using match and index. My table has many of these searches, although it should be easily verified.

In pure excel formulas, this is simple:

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

although it is easy to do, it created large hats if the size of the table changes, etc. the readability of the formula is also bad. Named ranges for the table, and headings and columns make troubleshooting difficult.

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

I call this range row and column names as the first row and column, respectively.

therefore, the Excel variable will be called test_table.

I want to write a VBA function with which I can call:

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

python DataNitro pandas, . VBA, VBA . , , , google -.

, ( @John Bustos ):

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

,

Answer 1

:

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

, .., !