Home » Excel Video Tutorials

Excel VLOOKUP Book By Chandoo. Org – Reviewed.

by

Now I have been an Excel enthisiast and user for around 20 years now, but one of the most talked about features in Excel I have experienced is the VLOOKUP table.  I recently changed over offices in my day job, and one of the first things someone said to me when they found out about my Excel exploits was ohhh so you know all about them VLOOKUPS and stuff. … Love them or hate them they are awesome in their power of analysis from just a few clicks of the mouse!

vlookup

A book/video I have had in my possession for a while is Chandoo’s VLOOKUP Book.  Well there are actually two versions of this book, there is the actual Ebook and also an Ebook+Video Combo Pack.

I have used it a good deal and it is a good Excel book, on a specialised subject, which is sometimes just what you need. Excel books can try to cover too much subject matter, but Chandoo really expores the Pivot Table Function well in his offering.

big-tickIt is really good solid Excel Book and warants a review.  So here it is – my review 🙂

 

As I said there are two flavours of the Book, the first is the standard Ebook – downloadable as a PDF or for your Kindle.  Included in this bundle is the below-

  • The Vlookup eBook (Kindle or PDF version)
  • Example Excel workbook with more than 50 formulas

The purpose of the book he states is to give you an understanding of how to answer any data lookup problem using Excel LOOKUP.

big-tickThe book is not overly theoretical but poses problems and walks you through the Excel solutions. He very quickly gets you past the basics of the VLOOKUP, building confidence in understanding the concept from the start.

big-tickI was impressed with how quick he gets you to thinks about solutions to data lookup situations you may have yourself and moves to expand on the standars VLOOKUP by intoducing the Index Match functions to feel you have even more Data Superpowers!

To give you an idea of content here are the details.

Who is Chandoo & What is Chandoo.org? .. 5
A note about downloadable workbook .7
Oh! Why should you bother reading this book?!?.. 8
What is VLOOKUP?…9
What about LOOKUP, HLOOKUP.. 14
Using VLOOKUP with Excel tables .. 17
Wild card searches with VLOOKUP .. 20
Let’s mix VLOOKUP with Data Validation for fun & wow factor .. 22
Look right, look left… oh forget! .. 26
What is the sales of Joseph in North for product “Fast car" in Jan 2013? ..29
I want to lookup my cake and eat it too! .. 37
2 way lookups won’t give you hiccups .. 41
2nd time lucky? No… how about third time? .. 46
Lookup Tips & Gotchas .. 50
How to learn more about Excel? .. 52
Appendix 1: INDEX Formula Explained .. 54
Appendix 2: Excel 2013 Data model ..55

big-tickI loved the Fact there a meaty Excel workbook download to work along with.  A must I think for any Excel book or course.

The Extra Feature COMBO pack as well as the PDF or Kindle book gives you access the following material below.

  • The Vlookup Book – PDF (right click and choose save as)
  • Chapter 1: Introduction to VLOOKUP [30mins]
  • Chapter 2: What about HLOOKUP and LOOKUP? [6mins]
  • Chapter 3: Using Tables with your lookup formulas [11mins]
  • Chapter 4: Wildcards in VLOOKUP [11mins]
  • Chapter 5: Lets make Bob happy [15mins]
  • Chapter 6: Looking up to left [20mins]
  • Chapter 7: Multi-condition Lookups [30mins]
  • Chapter 8: Getting several columns with single lookup [10mins]
  • Chapter 9: 2way lookups [30mins]
  • Chapter 10: Getting 2nd or 3rd matching item with lookups [30mins]
  • Chapter 11: Top 10 lookup tips [13mins]
  • Chapter 12: Bonus: Comparing 2 lists [8mins]
  • Chapter 13: Bonus: The duplicate problem [18mins]
  • Chapter 14: Bonus: Understanding Excel 2013 data model feature [16mins]

the-vlookup-book-ebook-combo

big-tickYou can see by how content rich the videos are how much content you get from Chandoo.  The videos are awesome quality and easy to follow and understand.

They really bring the examples to life and if you like watching to learn then this Combo pack will suit you.  I always like watching video instruction so this was the option for me.

 

So after working grab itmy way through the VLOOKUP book and instructional Videos, I have to give Chandoo a thumbs up for this product.  Can’t fault it.

 

 

 

In the spirit of full disclosure, this is an affiliate link, which means that I may get a commissions if you decide to purchase anything. I only recommend products & systems that I use and love myself, so I know you’ll be in good hands. Thanks for supporting this website.

Compare Two Excel Lists To Highlight Missing Items

by

A really common task used frequently in Excel is to compare two lists to highlight items that appear in one list but are not in the other. Here is a great way to combine two great Excel features to get exactly the results we need.

We can combine the VLOOKUP function along with Conditional Formatting. The VLOOKUP will be used used to look up an item from one list in the other, and the Conditional formatting will highlight the missing items for us.

In my example I have two lists of products and suppliers and we want to check this list with the new list on the same sheet which has 3 products missing.

 

  • Select the range of cells you wish to format, in my example it is A1:B21
  • Hit Conditional Formatting on the Home Tab
  • Click Use A Formula To Determine Which Cells To Format
  • Enter the VLOOKUP formula below into the field provided

 

=ISNA(VLOOKUP($A3,$D$3:$E$18,1,false))

compare 2 lists1

So, the way this works is the Vlookup is looking for a match in column A. If no match is found the the error #N/A is returned. The ISNA function will return true if a match is not found, hence the cells will be formatted. Nice huh?
In my example I have found the three missing products of my second list stored in cell range D3:E18.

 

 

 

 

 

 

 

 

 

 

 

More Excel Tips

Want More VLOOKUP Tips?

vlookup-book-cover

 

 

 

 

Why You Should Use MS Excel To Build Business Dashboards

by

dashboard_blog_example_3Dashboard skills are in great demand by employers, if you can build great dashboards; it is a valuable skill to have. Even better if you can build them in one of the most widely used pieces of software- Microsoft Excel.

Essentially dashboards are a technique of report used to visually present critical metrics or a data summary to enable quick and effective business decisions. This would be in the same way a car dashboard will display only the main pieces of data needed when driving your car.

There are a variety of dashboards around, you will no doubt have heard of Metrics Dashboard, Business Intelligence Dashboard, Operational Dashboard, Performance Dashboard, the list can go on.

Most users under use and underestimate the power of Microsoft Excel in particular when it comes to creating interactive dashboard reports. With business requirements changing at increasing speed, the need for clarify on business performance and metrics is in strong demand.

Here are some great reasons for using Excel to build your business dashboards:-

My favorite reason for using Excel is its Flexibility.

How many times has a manager asked for extra items, change the timing of the reports and how they look? Excel can easily accommodate these changes and allows users to quickly respond to changing requirements of business. This flexibility can allow users to add extra metrics, add in extra charts, replace graphs, and change emphasis by font size and color. Of course as well as Excel being part of the Office Suite allows ease of interaction between other Office products.

Powerful Analytical Capability.

Excel can handle complex statistical calculations, many of which are in-built as Functions which can then be easily displayed on a dashboard. Help on these in-built functions is readily available from the onscreen help and a walk through of all the elements of the function.

Ease of Distribution.

Excel users are everywhere, and no matter how good any other bespoke piece of software is to build dashboards, there will not be that many people who know how to use it, or be able to open it in a usable format. It is probable that nearly everyone involved in business in some way will at least heard of Excel, if not know something about the software. Team it up with Share Point and you have one powerful publishing platform.

Ease of Use and Learning.

A lot more organizations are realizing the benefits of empowering their own employees to analyze and present their own metrics for their own departments. You do not need an Excel Visual Basic expert to build dashboards when you get to know the correct combinations of worksheet functions.

Easily Identify And Fix Errors.

With Excel it is easy to test for errors in your data, you can search for missing values and highlight values that are unreasonably high or low or that you are just not expecting in your data set. Once you can see your errors you can easily then go about fixing them.

Superb Magazine Quality Reports.

Excel dashboards are not just easy to build, scalable, distributable and easily adaptable, they are also of a really high quality. Not many people take advantage of the full capability of the color schemes, fonts and charting design that is available right within Microsoft Excel.

 

If you need to take your Excel skills to the next level to get that dream job, or want to create informative and interactive dashboard reports to impress your boss or clients, then you really need to know Dashboards.

 

I am really excited to tell you that Mynda at My Online Training Hub.Com is running their fantastic Excel Dashboard Course again from 31st July 2014.

The course is video based and available online 24/7. It comes with comprehensive Excel workbooks and several sample dashboards to keep. There’s also an option to download the videos, plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

Dashboards are an incredibly valuable tool in today’s market for consultants, analysts and managers. But Excel doesn’t make it straightforward to build highly professional and interactive dashboards. That’s why this type of training is crucial.

What members are saying about the course:

The previous classes have been a huge hit with many people saying how they love the cool techniques and how they’ve been able to impress their colleagues and clients by using them in all sorts of reports, not just dashboards.

Others have said the course has prompted them to take a whole new approach to producing their monthly reports.

I had the pleasure of taking the course and I highly recommend the course but don’t take my word for it. You can read comments from past students and find out more here.

Bonus 20% Off

If you join the class by 24th July 2014 you can get it for 20% off.

So, do yourself a favour and check out the course. The price is incredibly fair, the course is awesome and it’ll transform your Excel reports and possibly even your Excel career.

 

FREE E Book

If you want some free Excel Dashboard Tips then feel free to download 30 Charting Excel Tips. No catches, just 30 brill Excel tips for Charting. If you feel like taking it a step further then sign up for the Excel Dashboard Course. You won’t regret it!

Keep Excelling!

 

 

 

Disclosure: I make a small commission for students who join Mynda’s course, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, but then you know that.

 

 

 

Quickie Tip: Shrink Your Cell Data

by

Here is another fab Excel tip.

Excel is kind of clever as if you didn’t already know. Excel will automatically fit your data to your cells by shrinking it. The data in the cell is fitted within your column width by the reduction of your font size- which then changes with the width of the columns. Yes clever!

So, if you want to shrink your data into your Excel cells.

  • Select a cell or a range of cells
  • Home Tab
  • Cells Group
  • Format
  • Format Cells
  • OR select CTRL+1 for even quicker navigation
  • Alignment Tab
  • Select shrink to fit
  • Hit Ok to save your settings

See in my short video tutorial below- Give this a go yourself its kinda funky.
 

Other Excel Tips And Tricks You Might Like

1. Unleash The Power Of Concatenation

2. Count Unique Items In A Range Of Cells Using Advanced Filtering

3. Create A Timestamp In Excel With Formulas

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar