5 Craziest Microsoft Excel VLOOKUP Tricks You Must Know

You may also like...

4 Responses

  1. Unknown says:

    Thank you for the tips, Today I have learned something new:)

  2. I enjoyed this article because it brought back some great spreadsheets in my past. On any job descriptions with Excel required, you also see the VLOOKUP as an example of Excel knowledge. These tricks, summarized by Buzz Analysis, give a great understanding of the functions abilities and especially error messages.

    However, If ever you want to step up those equations, may I suggest the following additions. Note: Enhancing equations in Excel can feel like building on a recipe at a 5 star restaurant…as least for Excel lovers like me.

    Let's start with our basic VLOOKUP, add an equal portion of HLOOKUP, (for a horizontal data search; the V in vlookup is for vertical). Infuse the two flavors with an INDEX function, some more commas and parenthesis.

    Reality example here:
    You have a chart of Store Sales by month, with "Sales by Months" across the top of the table and "Store Locations" down the left side. You can use and INDEX to find a specific stores sales for a specific month.

    = INDEX(VLOOKUP(what,where,count-across,false),HLOOKUP(what,where,count-down,false)

    Back to the recipe…Next, slowly add a the MATCH function, noted above in trick #5, within both the HLOOKUP and VLOOKUP functions. If you are "error intolerant", add trick #4, ISERROR function, to the equation. Lastly for those with a distinct pallet, you can substitute, trick #3 for any data range.

    Obviously, I enjoy this stuff to much. But what an Excel power rush! Yum

  3. Thanks Donna Chamberlin for sharing..

Leave a Reply

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