Home Archives for VLOOKUP Function
October 25, 2010 Leave a Comment

How to Use the VLOOKUP Function in Excel

The VLOOKUP Function is one of the essential Excel functions that you need to understand in order to work with data tables, invoices and product listings. In response to numerous email requests, I created this video lesson to walk you through each step.

I share my best practices which include:

  • Use a “named cell range” for your “array.” The “array” is the table that contains the information that you are looking up.
  • Make sure that the “Leftmost” column in your array is sorted in ascending order and that this column contains the values that you are looking for.
  • Use “FALSE” as the optional fourth argument when you need to find an “exact” match in the Leftmost Column.

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

You can learn more about essential Excel Functions on my DVD, “The 50 Best Tips for Excel 2007.” You can buy it now!

Learn how to “Master Excel in Minutes – Not Months!”

How to Use the VLOOKUP Function in Excel [ 5:36 ] Play Now | Play in Popup | Download (1006)
Link to this post!
Filed Under: Excel Functions, Excel Training, Interactive Webpages, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
September 24, 2010 Leave a Comment

List of My Most Popular Posts and Pages

Summer – in the Northern Hemisphere – is now behind us. I went back to survey the videos and pages on my website that had the most views during the Summer of 2010. 

Here is a listing of my “Top 10 Video Posts” and my “Top 5 Pages.” Click on the Links for each title to go directly to the Video Lesson or Page.

The “Top 10 Video Posts” on The Company Rocks Website:

  1. Simplify Data Lookups in Excel
  2. Make Excel Come Alive with Visualization Tools
  3. Build an Accounts Receivable Aging Report
  4. Summarize Multiple Excel Worksheets – Consolidate Data by Position
  5. How to Merge Multiple Excel Workbooks into a Master Budget
  6. Use Pivot Tables to Summarize by Year, Quarter and Month
  7. Perform Break Even Analysis with Excel’s Goal Seek Tool
  8. 2 Reasons to Format Cells Before You Enter Data in Excel
  9. Hide and Show Details in Excel Pivot Tables
  10. Use Vlookup Function to Prepare a Payroll Statement

The “Top 5 Pages” Viewed on The Company Rocks Website:

  1. The Excel Home Page
  2. Index to Excel Video Lesson Topics
  3. List of “The 50 Best Tips for Excel 2007”
  4. Excel Training Videos by Category
  5. The 50 Best Tips for PowerPoint 2007

The Danny Rocks Tips and Timesavers Podcast

During the Summer of 2010, I started my own video podcast at the iTunes Store. These podcasts are free of charge. The video resolution is optimized for viewing on an iPad, iPod or other portable media device.

The DannyRocksExcels Channel on YouTube

Over the past two years I have posted 104 video lessons on my “DannyRocksExcels” Channel on YouTube.

The DannyRocksPowerPoint Channel on YouTube

I maintain a separate channel on YouTube – “DannyRocksPowerPoint”– to post my PowerPoint Tips and Narrated PowerPoint Presentations. I invite you to vist my channel to view, download and share these videos with your friends and colleagues. Click here to go to my YouTube Channel.

“The 50 Best Tips for …” series of DVDs

I offer five DVDs for sale on this website. Click here to start your shopping cart. I guarantee your satisfaction. If you are not 100% satisfied with my DVDs, I will refund your purchase with no questions asked!

You can learn how to “Master Excel in Minutes – Not Months!”

Here is a listing of the DVDs that I offer for sale. Click on the link for each title to go to the list of the tips that I offer on each DVD:

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, Powerpoint Techniques Tagged: , , , , , , , ,
September 21, 2010 Leave a Comment

How to Use Excel Lookup Functions to Create a Customer Invoice

Excel has many powerful Lookup Functions. You can use them, for example,  to lookup an Item Number when you know the Product Description. Or to automatically look up the List Price for an Item Number on your customer invoice. In this lesson I show you how to use three Excel Lookup Functions:

  1. The INDEX() Function
  2. The MATCH() Function
  3. The VLOOKUP() Function

I hope that you enjoy learning – and applying – these techniques. I would like to hear from you, so you can either add a comment below or send me an email.

You can receive my new Tips and Timesavers Videos automatically by subscribing to my RSS Feed or to my free podcast on iTunes. Click here to view, download or subscribe to my podcast.

You can purchase any of my 5 DVDs in “The 50 Best Series …” for Excel 2007, PowerPoint 2007, Word 2007, Outlook 2007 and Access 2007 – Click here to open your shopping cart!

Learn how to “Master Excel in Minutes – Not Months!”

How to Use Excel Lookup Functions to Create a Customer Invoice [ 5:24 ] Play Now | Play in Popup | Download (789)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers, Uncategorized Tagged: , , , , , , , ,
May 15, 2010 1 Comment

How to Compare Two Excel Lists for Differences

Lists, lists, lists … It sometime seems that I am swimming in a sea of lists. Lists of customers, updated lists of customers, sales by week, sales for the current week, book lists, revised lists of books, etc. Lists seem to beget more lists and both beget problems – How to compare pairs of lists to avoid duplicates and redundancies!

If you can relate to this scenario, I know that you will enjoy this Excel Video Lesson.

Three Techniques Used in My Tutorial

1) Use the =MATCH() Function

2) Use the =VLOOKUP() Function

3) Use a Pivot Table in Excel 2007

Shop at My Secure Online Shopping Site

If you enjoyed this Excel Video Lesson, I am confident that you will enjoy the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.”  Click on this link to visit my secure online shopping website.

You can watch this Excel Video Lesson on YouTube – Subscribe to my YouTube Channel – DannyRocksExcels

Watch My Video Now

 

Related Excel Video Lessons:

Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Pivot Tables Tagged: , , , , , ,
August 11, 2008 4 Comments

Use Vlookup function to prepare a payroll statement

A viewer wrote to me, asking for help in preparing his company’s payroll statement. I found an Excel Template using the =VLOOKUP() function to help him. In this Excel Video Training lesson, I demonstrate how to effectively use Lookup functions from 2 different worksheets.

Here are the steps to follow in this Excel Video Training Lesson:

  1. For the 1st part of this lesson I use an Excel Template called Payroll which I downloaded from the MS Excel website.
  2. The Payroll Statements draw information from two different worksheets via the =VLOOKUP() Function.
  3. Make sure that your “table arrays” have the key values in the 1st (leftmost) column.
  4. To return an “exact match” type in FALSE for the 4th argument.
  5. If you want to copy your =VLOOKUP() formulas, use “Absolute Cell” references for the table array.

Find the Excel Video Training Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Functions, Excel Training, MS Excel Training, Video Response to Viewers Tagged: , , ,
August 10, 2008 4 Comments

Use Excel's =VLOOKUP() function to produce an invoice

Excel’s =VLOOKUP() function is used in almost every business form that we encounter – Customer invoices or Employee Payroll statements, etc. This short training video will show you how and when to use the Vertical Lookup function.

Here are the steps to follow in this Excel Video Training Lesson:

  1. The key to understanding the =VLOOKUP() function is to look for a “value” in the 1st column (leftmost) of a table array. So bear this in mind when you construct tables for Price lists, Employee Information, Inventories, etc.
  2. 3 Arguments are required. The 1st argument – “What value do you want to look up?” You can either enter a value or point to a cell reference.
  3. The 2nd argument asks for the location of the table array. Select the entire table, but NOT the column headers.
  4. The 3rd argument asks, “Once I have found the value in the table, what information do you want returned in your formula?” This is the Column indes i.e. counting from left to right, what is the 3 of the column that contains the information.
  5. The 4th argument is optional – enter “FALSE” if you require an exact matching value.

Find the Excel Video Training Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , ,
« Previous Page