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.

Excel VLOOKUP Videos

You don’t need any special equipment or fancy telescopes to do a lookup in Excel — you just need a simple formula.

In my videos below, see 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.

First, here’s a video that shows a basic VLOOKUP formula to get the correct price from a product price lookup table.

___

Excel MATCH Function with VLOOKUP

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

Approximate Match in VLOOKUP

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.

More VLOOKUP Tips and Videos

You can head over to my Contextures website, and see more VLOOKUP function examples and videos.

That page also has tips for troubleshooting VLOOKUP problems, like the formulas shown in the screen shot below.

After the list was sorted, the VLOOKUP formulas ended up in the wrong order, and show the wrong product prices!

VLOOKUP returns incorrect result after sorting
VLOOKUP returns incorrect result after sorting

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 thoughts on “Celebrating VLOOKUP Week”

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

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

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

  4. @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),)

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

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

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

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.