How to Use the Offset Function Inside a VLookup in Excel

Vlookup in Excel

VLOOKUP in Excel

Each month, I get 5 to 6 emails of phone calls from viewers who who are having trouble using the VLOOKUP Function in Excel. In the majority of cases, the problem is that their Data Table is setup so that the “Key” field that they need to search in is NOT the Left-most or first field in the table. It is not always practical – nor desirable – to use “Cut and Paste” to rearrange the fields in the data set. So, what techniques can you use to fix this?

Use the OFFSET Function Inside a VLOOKUP Function

In this scenario, our IT Department sends us a daily report of the products that we sell including fields for the current cost and quantity on-hand. We use this report to generate additional reports and filters. This report gets saved automatically as an Excel 2010 Table with the name “Inventory.” The problem is, whenusing a VLOOKUP, that the first – or Left-most – field is NOT the “Key” field that we want VLOOKUP to use to search for a Match in order to return the current price or inventory for individual product.

The OFFSET() Function in Excel, makes it easy to reference a “starting field” that, in this case, is one column to the Right. This is perfect for our situation. We can continue to use our “named range” with the VLOOKUP!

Watch Video in High Definition

Follow this link to watch this tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Visit My New Secure Online Shopping Website

I invite you to visit my new online shopping website at: http://shop.thecompanyrocks.com I have created new, extended length video tutorials that you can either purchase to download immediately or order them on a DVD-ROM. In addition, I have published versions of my 90 minute Pivot Tables Video Tutorials for Excel 2003, Excel 2007 and Excel 2010. So, I have the tutorials that you want for the Excel version that you are currently using!

Click to Play Excel Video Tutorial

Structured Formula References in Excel 2007 and Excel 2010 – Part 2

Compare Structered References Excel 2007 v 2010

Compare Structured References 2007 v 2010

This is the second and concluding part of this series where I demonstrate how to use Structured Formula References when referring to Excel Tables.

Key Points Covered

  • The difference between Structured Formula References in Excel 2007 and Excel 2010
  • How to Edit Structured Formula References
  • How to Write Structured Formulas using the new Formula AutoComplete drop-down menu

 

Watch My Video Tutorial Now

 

Watch Video in High Definition on YouTube

Click on this link to watch this Video in High Definition on my YouTube Channel – DannyRocksExcels

Go to Part 1 in this Series of Video Tutorials

Follow this link to go to Part 1 in this Series of Tutorials about using Structured Formula References in Excel Tables.

Visit My New Secure Online Store

I have just published 6 New Products and opened a new, revised, secure online shopping website. Take a look at: http://shop.thecompanyrocks.com

 

How to Maintain Accurate Subtotals When Your Data Set Expands in Size

Subtotal in Excel 2010 Table

Subtotal in Excel 2010 Table

This is Part 2 of my series of video tutorials demonstrating how to use the SUBTOTAL Function in Excel.

  • In Part 1, I showed you the value of using the Subtotal Function to summarize the results of applying a Data Filter to a range of cells.
  • In this part, I show you how to use an Excel 2007 or Excel 2010 Table to ensure that your Subtotal Formulas are automatically updated when you append records or add additional fields to your original data set.

I strongly recommend basing Filtered Lists and Pivot Tables on an Excel Table (in Excel 2007 or 2010) or an Excel List in Excel 2003. This way, any formulas, filters and references that you make will be automatically updated when you append additional records or otherwise change the structure of your data set.

Function Numbers 101 through 111

Notice that when you “toggle on” the Total Row for a Table or List that Excel uses this formula = SUBTOTAL(109, Table1, [Sales]). Function 109 will use the SUM Function(109) to total the values in the “Sales” field ([Sales]) of a Table named “Table1.” These Function Numbers + 100 were introduced in Excel 2003 and the are automatically applied whenever you are using a Total Row in an Excel Table.

I think that you will learn some cool tricks in this lesson. Let me know what you think!

Watch This Video in High Definition

Click on this link to watch this video tutorial in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels.

Invitation to Visit My New Online Shopping Site

I invite you to visit my new, secure online shopping website – http://shop.thecompanyrocks.com

Once there, you can get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007”

How to Find and Delete Duplicate Records in Excel 2007

It is easy  – much too easy – to allow duplicate records to creep into an Excel database. Fortunately, starting in Excel 2007, it is really easy to first find and then delete duplicate records.

Remove DUplicate Records

Remove Duplicate Records

With the price of postage climbing, you cannot afford to send duplicate copies of a mailing piece. Not to mention that doing so reflects poorly on your company’s organizational skills. Likewise, having duplicate account entries makes for extra work and confusion.

Use Conditional Formatting to Highlight Duplicate Records

Starting in Excel 2007, Conditional Formatting got a lot easier to use. One of the menu selections is “Show Duplicate Records.” Now, with just a few mouse clicks, you can format the records that show duplicate values in your data set. I like to use this technique prior to acutally duplicating records so that I can get a sense of the number of possible duplicate records in my data set.

Selecting Duplicate Records to Delete

As a best practice, I always make a backup copy of my data set before I actually delete records. This way, I can quickly recover from any accidental deletions of non-duplicate records. When you choose the command to “Remove Duplicates,” a dialog box opens to reveal each field in your data set. Generally, you want to select all of the fields to narrow down the list of records to delete. Since I have already previewed the possible duplicate records by using Conditional Formatting, I have a pretty good idea of how many records will be deleted. You do not see a preview of the exact records that will be deleted, so make sure that you have a backup copy of your original list in order to restore any “accidental” deletions.

These new commands and menus introduced in Excel 2007 make deleting duplicate records a very quick process. Use them – wisely – to save yourself time, confusion and money!

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

Watch this video lesson in High Definition, Full Screen mode. Follow this link to view this on my YouTube Channel – DannyRocksExcels

Secure Shopping Cart

Secure Shopping Cart

This is one of the tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” CLick here to open a secure shopping cart to purchase it.