Structured Table Referencing and VLOOKUP

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:

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.

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

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.

13 thoughts on “Structured Table Referencing and VLOOKUP

  1. 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)

  2. Í 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

  3. 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!

  4. OK, OK, I know when I’m beat.

  5. 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.

  6. 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

  7. Required yes. But I always include both argument with INDEX. Especially if the code is creating the formula, there’s no downside.

  8. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *