Search code examples
vbaexcelworksheet-function

Return matching named table in worksheet function VBA

Does anyone know how to return a values in a table with:

WorksheetFunction.Match

Or this there any other alternative function that could be used in order for it to be working.

rowNum = WorksheetFunction.Match(sum.Range("coverage").Table, temp.Range("A1:A200"), 0) 'colNum = WorksheetFunction.Match(sum.Range("Header").Value, temp.Range("CoverageT"), 0)

Above I tried to use WorksheetFunction to get it to work but that was a fail. I named tables 'coverage and 'header to get it to function but I did not succeed.

I hope my problem is clear enough to get some help! If it's not clear enough please leave a comment!

Solution

  • WorksheetFunction.Match gets you the relative position of a value in a single column range or a single row range. If your item is third in the list it returns 3.

    If you want the value from a table use WorksheetFunction.VLookup. You are looking up your item in the first column of a table or range. You specify which column you want the value from and it will return the cell value from the matching row.

    Or use HLookup for a transposed table.

    Try this if you really want to use Match: 

    dim KeyValue as string
KeyValue = "my item"

Dim rowNum as Variant
If Not VBA.IsError(Application.Match(KeyValue, temp.Range("A1:A200"), 0)) Then
    rowNum = Application.Match(KeyValue, temp.Range("A1:A200"), 0)
End If

    Try this if you want a vlookup:

    Dim RetVal As Variant
Dim KeyValue As String 'or integer or date as appropriate
Dim LookupTable As Range
Dim ColumnNumber As Integer

KeyValue = "myItem"
LookupTable = Range("A1:A200")
ColumnNumber = 2
RetVal = WorksheetFunction.VLookup(KeyValue, LookupTable, ColumnNumber, False)