VLOOKUP Formula Cheat-sheet – FREE Download

Posted on November 19th, 2010 in Learn Excel - 13 comments

FREE VLOOKUP Formula Cheat-sheet - Download todayWe have talked a lot about VLOOKUP and other lookup formulas recently during VLOOKUP Week.

In this post I am giving away a free VLOOKUP formula cheat-sheet for our readers. This cheat-sheet is prepared by Cheater John specifically for our readers. I hope you enjoy the one page help on VLOOKUP.

Click here to download VLOOKUP Cheat-sheet. [mirror]

More resources on VLOOKUP & Other Lookup Formulas:

Thanks Cheater John

Thanks to Ates, the person behind Cheater John for preparing this beautiful VLOOKUP cheat sheet and sharing it with us. Visit his site if you are looking for more such cheat sheets.

If you enjoyed this cheat-sheet, please take a minute to say thanks to Cheater John.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

13 Responses to “VLOOKUP Formula Cheat-sheet – FREE Download”

  1. Mike says:

    Hi Chandoo, I normally use Index which is much more powerful than vlookup. However, I learnt a new trick that allows you to use “Vlookup” to look up values to the left without helper columns, etc. I left it at your link:

    http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/

    It was a bit late so you most likely have not seen it, anyway, you can use Choose() to look up columns to the left. In your example on that link the formula returns the value to the left:

    =VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0) = “John”

    Using Choose() allows you to look up values to the left.

    regards, Mike

  2. Chandoo says:

    @Mike: in fact, I did notice your comment about using CHOOSE() to overcome VLOOKUP’s limitation. I wanted to congratulate you for finding such beautiful alternative. But I guess I must have lost in the flow of new comments. Thanks for the reminder and sharing it again.

  3. godzilla says:

    @Mike and Chandoo. I would love to see a detailed explanation of this if you have the time. Thanks for the great print out it is up on my desk. I use look ups all the time but it is always nice to refer back to something.

    thanks

  4. Gopinath says:

    The CheetSheet gave a very good explanation on 3 way lookups.

    Thanks

  5. Hui... says:

    @Godzilla
    RE: =VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
    .
    The standard form of VLOOKUP is
    =VLOOKUP( Value, Array, Col#, 0)
    In the standard form VLookup looks for the Value in Column 1 of the array and then returns the matching value which is in Column Number, Col#
    .
    What Daddylonglegs has done with
    =VLOOKUP( 1088, CHOOSE({2,1}, $B$5:$B$17,$D$5:$D$17), 2, 0)
    .
    Is to use Choose to define a 2 Column array in 2 parts with
    CHOOSE({2,1}, $B$5:$B$17,$D$5:$D$17)
    where Column 2 is defined to the left of Column 1.
    .
    Vlookup will go to Column 1 and look for the value and then go to Column 2 to find the matching value
    .
    It is worth noting that
    =VLOOKUP(1088,CHOOSE({1,2},$D$5:$D$17,$B$5:$B$17),2,0)
    returns the same value which is should.
    .
    The method can be extended to any number of columns
    =VLOOKUP(2133,CHOOSE({1,2,3},$D$5:$D$17,$B$5:$B$17, $C$5:$C$17),3,0)
    returns the No of Customers

  6. Mike says:

    Yes it is really quite amazing, I discovered it on excel forum where “daddylonglegs” used it and thought I would pass it on as I had always been under the impression that it was not possible to look up values to the left using vlookup without creating helper columns. I am sure that the way that “choose” works by creating an array in this fashion makes it usefull for other applications.

    It is also helps to see how it is working by creating an array with it i.e. select a range and enter below into cell and creat array by entering cntr+shift+enter:

    =CHOOSE({1,2,3},$D$5:$D$17,$B$5:$B$17, $C$5:$C$17)

    You should see the values in the order that you selected them from {1,2,3}

    Just shows you that you never stop learning.

    Mike

    PS your site is great chandoo and look forward to all your posts.

  7. Eric says:

    Here’s an advanced use of VLOOKUP that might help someone out…

    I have a database of dates, employees, and hours worked. The table (data only) is a named range HTable, and each of the columns data are named ranges as well (Dates, Employees, and Hours), amongst others. The table gets sorted all sorts of ways, so there is no guarantee that any column will be in any order. To look up the hours worked by a certain employee (certain_Employee) on a certain date (certain_date), use this:

    {=VLOOKUP(certain_date, HTable*IF(Employees=certain_Employee, 1, 0), 3, FALSE)}

    Effectively, you are looking up first on column 2 (zeroing out all but the certain_Employee) and then on column 1.

    Don’t forget to use ctrl-shift-enter to get the braces.

    Eric

  8. Gururaj says:

    Hi john & chandoo

    thanks

  9. [...] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk [...]

  10. behnam says:

    hi my friend :
    pleased send for me a complete file to my email address that explain for me that use about – v look up function in excel .
    truly yours
    behnam danesh
    july – 25- 2012

  11. [...] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk [...]

  12. […] This link should help: http://www.contextures.com/xlFunctions02.html Chandoo is also quite good: FREE Excel VLOOKUP Formula Cheat-sheet – Download Today | Chandoo.org – Learn Microsoft Excel Online […]

Leave a Reply