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:
|
1 |
=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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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
|
1 |
=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.
