Celebrating VLOOKUP Week

image Over at Chandoo's Excel blog, he's celebrating VLOOKUP week, with helpful posts like VLOOKUP Formulas Go Wild. Who knew an Excel formula could go wild? I've seen many workbooks where things have run amok, but fortunately, Chandoo's examples are much better behaved.

You don't need any special equipment or fancy telescopes to do a lookup in Excel -- you just need a simple formula. Chandoo explains how to use the VLOOKUP function, and overcome its few shortcomings with other functions, like INDEX and MATCH.

Watch the VLOOKUP Videos

I'm a big fan of VLOOKUP too, and have made several Excel VLOOKUP videos that show you how to use the function in different scenarios.

For example, here's a video that shows you how to use the Excel MATCH function within a VLOOKUP formula, to make it more flexible.

The next video show you how to use VLOOKUP to change a student's numeric score into a letter grade, by using the Approximate Match option.

You can head over to the Contextures website, and watch the rest of my Excel VLOOKUP videos, to see a few more examples of how to use it.

Celebrate VLOOKUP Weekend

With all this information on VLOOKUP to read and watch, you won't have to worry about planning any other weekend activities. Chandoo's VLOOKUP Week might end today, but you can continue celebrating, by creating your own VLOOKUP Weekend. Have fun!

____________

10 comments to Celebrating VLOOKUP Week

  • Kirill Lapin (KL)

    VLOOKUP() is no doubt a very handy function when performing a limited number of lookups in a fairly limited range or array. However, it is highly calculation intensive, and may (and very often does) generate all sorts of workbook performance problems for users who are not aware of that. VLOOKUP() can also be represented as INDEX(Range,MATCH()) where MATCH() is the heaviest calculation part. So when you use VLOOKUP() for the same searched value N times, you inevitably repeat the search N times. Those who use VLOOKUP() to return multiple fields from a large database based on an ID are much better off using INDEX(Range,MATCH()) construct due to the following:
    1) you can isolate MATCH() into a intermediate column and use INDEX(array,A1) to return values from multiple database fields, thus searching the database only once for each record and significantly reducing the calculation overhead.
    2) INDEX(Range,...) uses the actual reference to the column containing the results, while in VLOOKUP(,,2,) people tend to hard-code the column. Thus if you rearrange the columns in the database you'll get an erroneous result
    3) unlike VLOOKUP(), INDEX(Range,MATCH()) doesn't require the search array to be to the left of the results array, to be parallel to it or to start from the same row.
    Overall, INDEX(Range,MATCH()) is way more efficeient for multiple fields calculations and much more flexible. There is only one thing that occurs to me and that INDEX(Range,MATCH()) can't do as compared to VLOOKUP(): to return an array of results like in =VLOOKUP(A1,B1:E10,{2,3,4},0)

  • These examples are two perfect compliments to what Chandoo is doing this week. I like your examples because they're practical, and hence useful.

  • Thanks Kirill, for your detailed explanation of the benefits of using alternatives to VLOOKUP.

  • Jason Morin

    Agree with most everything KL says. The problem I have found is that while most Excel users I work with grasp VLOOKUP, they struggle with the INDEX/MATCH contruct.

  • Very good video on using Match with VLookup. We deliver Excel training courses and my Intermediate learners would appreciate this as a post-course resource.

  • sam

    @Krill
    Index/Match can return an array

    =Index(RectRng,,3) will return a row array of Column 3
    =Index(RectRng,3,) will return a column array of row 3

    So VLOOKUP(A1,B1:E10,{2,3,4},0) is same as
    =Index(C1:E10,Match(A1,B1:B10,0),)

  • Kirill Lapin (KL)

    @Sam
    Yes you're right :-) I should have been more precise as I meant it can't return an array of results for non-consequent columns:
    =VLOOKUP(A1,B1:E10,{2,4,10},0)

  • Patel

    I had a question about "$" sign in VLOOKUP. Is there any way we can customize vlookup formula so that while selecting the Range, "$" sign always populates and makes the range constant

  • Kirill Lapin (KL)

    @Patel,
    No, you can't do that, but you can assign names to your tables (XL2003: menu Insert->Name->Define... or XL2007/2010: tab Formulas->Name Manager):
    MyTable =[D1:F20]
    =VLOOKUP(A1,
    MyTable,2,0)

  • Kirill Lapin (KL)

    Sorry, I meant this:

    MyTable =$D$1:$F$20]
    =VLOOKUP(A1,MyTable,2,0)

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>