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.
You say toe-may-toe. I say tow-ma-tow.
You say VLOOKUP. I say INDEX and MATCH.
Let’s call the whole thing OFFSET.
You only need that macro, because you’re using a refer-to-the-whole-damned-table VLOOKUP, instead of just-point-at-the-two-things-I-care-about INDEX/MATCH.And the fact that a VLOOKUP functions points at an entire table of data – even though it only ever actually makes use of two columns of it (the key column on the far left, and the nth column given by the col_index_num argument) – means that any time you change any cell in that table, your VLOOKUP is going to recalculate. And then so are ALL formulas downstream of those VLOOKUPS. It’s exactly as if VLOOKUP is a volatile function, where changes to that that Table is concerned.
Whereas with INDEX/MATCH, your functions will ONLY recalculate if you change something in the specific columns that the INDEX and MATCH combo references.
Given that, I’ll enter INDEX/MATCH any time the old fashioned way. (Although I do intend to write a routine that does a global replace of any VLOOKUPS with INDEX/MATCH from any toe-may-toe eaters who’s work I inherit)
Í agree with Jeff, I believe that using INDEX/MATCH is a killer in Tables.
“You only need that macro, because you’re using a refer-to-the-whole-damned-table VLOOKUP, instead of just-point-at-the-two-things-I-care-about INDEX/MATCH.”
+1
I’ve become a fan of INDEX/MATCH as well. It works extremely well with structured tables since you don’t have to set up named ranges. That used to be my biggest problem with INDEX/MATCH in PS times (prior to structured tables). I feel this is just as understandable to others as a modified bulletproof VLOOKUP and much easier for me to set up than using the COLUMNS trick to find a column.
Second, when I am matching multiple items between two tables, I move the MATCH into a helper column. The formula in my [MatchRow] column might look like:
=IFERROR(MATCH([@StkNO],TableDetails[StkNumber],0),0)
The Index formula can be copied to multiple columns and will be easy to modify to pull the correct results. On large tables, this means I only have one column with the lookup overhead.
=IF([@MatchRow]>0,INDEX(TableDetails[Cost],[@MatchRow]),””)
A second Index might be:
=IF([@MatchRow]>0,INDEX(TableDetails[Location],[@MatchRow]),””)
There is basically no performance hit for this second INDEX. A second VLOOKUP would double the workload.
Of course, if you are only pulling one column, then the work of creating the helper column isn’t necessary. Separating MATCH from INDEX has helped me with formula diagnosis the odd time though. Also, sometimes I just need to know if something is in both tables, so MATCH can stand on its own.
With all these variations possible using INDEX/MATCH, I just default to it, and not bother with VLOOKUP, even when it would handle things just fine.
I’m commenting from the point of view of a front line Excel user and what I found works for me. You guys are far beyond me when it comes to developing.
BTW, I say “toe-may-toe”. Jeff is just strange!
OK, OK, I know when I’m beat.
BTW, that turns
into
I know you think I should just learn to enter INDEX and MATCH instead of relying on a macro to convert it, but I’m being pragmatic. I resolved to never use VLOOKUP again a couple years ago and it just wouldn’t stick. So this is my compromise. Don’t judge me.
Hi Dick
I’m afraid I have to agree with others regarding Index/Match.
With your new vba conversion, your trailing “,1” in the formula is superfluous.
All that is required is
Came here to say the same as everyone else, was beaten to it.
Derek…the more people that nag Dick, the better. :-)
Required yes. But I always include both argument with INDEX. Especially if the code is creating the formula, there’s no downside.
A point I haven’t seen made yet: Users tend to use multiple columns of VLOOKUP’s next to each other with incrementing column number arguments. This means looking up the same row of data over and over. So to get better performance, move out the lookup bit to an extra column which just gets the row number using MATCH and then use the subsequent columns to pull the data using INDEX.
@jkp: seems Omar make a similar point above? Indeed one would think that splitting the match part out would make a significant difference but discussion from another post suggests the gain is less than one might expect unless the lookup ranges are sufficiently large. (http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/)
@Lori: You’re right, I overlooked that!
The original problem has a much simpler solution. I can’t take credit for it, because I had found it elsewhere and forgot I had used it in another Excel sheet. Above, Dick uses:
=VLOOKUP(D7,tblData,COLUMN(tblData[Name]),FALSE)
The following looks complex, but is just a few click to generate:
=VLOOKUP(D7,tblData,MATCH(tblData[[#Headers],[Name]],tblData[#Headers],0),FALSE)
As you enter the formula, type “MATCH(” (without the quotes, of course), Highlight the name of the column you want in the result (Name) and type “,” and now highlight the whole row of headers on the table, then type “,0)” and the column number will be looked up in the header row without forcing the table to ALWAYS be in column 1 on a separate spreadsheet.
I may have missed the point but:
VLOOKUP(D7,tblData,COLUMNS(tblData[[col1]:[Col3]]),0)
Then is doesn’t matter where your table is!