Structured Table Referencing and VLOOKUP

Nov12
by on November 12, 2015 at 12:56 am
Posted In: Around the Web, Lookups, Tables

Way back when, I wrote a post about naming columns that turned into a post about VLOOKUPs and MATCHes using structured table references. As usual, there was gold in them there comments. Since then I’ve been using the COLUMN() function in my VLOOKUP formulas to identify the return column. Like this:

=VLOOKUP(D7,tblData,COLUMN(tblData[Name]),FALSE)

This has the restriction that your table start in A1. Mine do. One table per worksheet and it starts in A1. This has been working well for me, but I’m sick of typing the COLUMN part. So I made a macro and here it is.

Private Sub mxlApp_SheetChange(ByVal sh As Object, ByVal Target As Range)
   
    Dim vaSplit As Variant
   
    If Target.CountLarge = 1 Then
        If Target.HasFormula Then
            Application.EnableEvents = False
                If IsTableVlookup(Target.Formula) Then
                    vaSplit = Split(Target.Formula, ",")
                    vaSplit(2) = "COLUMN(" & vaSplit(1) & "[" & vaSplit(2) & "])"
                    Target.Formula = Join(vaSplit, ",")
                End If
            Application.EnableEvents = True
        End If
    End If
   
End Sub

Private Function IsTableVlookup(ByVal sFormula As String) As Boolean
           
    Dim vaSplit As Variant
    Dim bReturn As Boolean
   
    Const sVL As String = "=VLOOKUP"
    Const sTBL As String = "tbl"
   
    On Error Resume Next
        vaSplit = Split(sFormula, ",")
       
        bReturn = Left$(sFormula, Len(sVL)) = sVL
        bReturn = bReturn And Left$(vaSplit(1), Len(sTBL)) = sTBL
        bReturn = bReturn And (vaSplit(3) = "TRUE)" Or vaSplit(3) = "FALSE)")
        bReturn = bReturn And InStr(1, vaSplit(2), "COLUMN(") = 0
       
        IsTableVlookup = bReturn
   
End Function

The IsTableVlookup function needs a little help. I just kept adding conditions as I tested it (and broke it), but I should probably just rethink the whole thing. Or maybe not, we’ll see. With this little gem in my custom class that handles application events, I can type

=VLOOKUP(D7,tblData,name,FALSE)

and it will insert the COLUMN part for me. I could even modify it for tables that don’t start in A1 by subtracting the COLUMN of the first column. The function also assumes you prefix all of your tables with “tbl”. You can change to suit. If you don’t use a naming convention, then I guess you’ll have to loop through all the tables and see if it matches one.

In an effort to be more like Debra, I made a video. As with every video I record with CamStudio, the first few seconds are blurry. I don’t know why.

└ Tags: