fbpx
Search
Close this search box.

VLOOKUP Formula Cheat-sheet – FREE Download

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

21 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 […]

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

  14. […] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUPuntuk […]

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

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

  17. Priyanka says:

    I have 2 excel files. One has names that are checked and the other has the complete list. How do i use VLOOKUP to check those names that are done in the complete list?

  18. ramesh kumar says:

    very easy & smooth process mathematics system.

  19. Thanks says:

    I have a real big problem at school with lookup and goal seek

  20. Laxmikant K says:

    From a yearly table (A) as below
    EMP No month shares loan
    10001 OPB Bal 10000 20000
    10001 APR-23 10500 190000
    10001 MAY-23 11000 180000
    to
    10001 MAR-24 16000 80000
    This sheet contains entries for around 600 employees. For new year I need to copy the MAR-24 shares and loan figure in new sheet(B) for new year starting from OPB BAL, APR-24, MAY-24 to MAR 25.

    I filter the sheet (A) on MAR-24 and copy each item individually,
    If I copy the filtered cells, they get copied to the next sheet continuosly.

    Can you suggest me better option to copy the MAR-24(A) row to new sheet(B) OPB BAL. This will help me a lot.

    Laxmikant M Khairnar
    Nasik - India

Leave a Reply