Lookup Functions In Excel | The Company Rocks

Danny October 4, 2012 Leave a Comment

How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

Essential Functions – VLOOKUP()
Text Functions – LEN() and TRIM()
Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about:

Watch Video in High Definition

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

Share and Enjoy:

How to Correct #N/A Error Messages for Excel's VLOOKUP Function [ 6:02 ] Play Now | Play in Popup | Download (7514)

Filed Under: Excel Basics, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, Text Functions Tagged: #N/A Error Message in Excel, Exact Match for VLOOKUP, Fixed Width Fields in Excel, Formula Errors, Len() Function, Paste Special Values, Trim() Function, VLOOKUP Function

Danny October 2, 2012 Leave a Comment

The 10 Most Watched Excel YouTube Videos for September 2012

My YouTube Channel – DannyRocksExcels – had 141,586 individual views during the month of September 2012:

Here is a list – with Hyperlinks – to the 10 Most Watched Excel Videos

Thanks to my 4,500 Subscriber on YouTube!

I extend my heartfelt thanks to my many loyal viewers and subscribers to my Excel video tutorials. Your feedback and requests for solutions have formed the foundation for my Excel Training Videos.

9 Essential Excel 2010 Skills

Learn More About my Extended Length Excel Video Training Resources

I have created a series of Extended Length Video Training Resources in the category of “Master Excel in Minutes.” I invite you to follow this link to learn more about my new series of videos.

Share and Enjoy:

Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Forms, Excel Formulas, Excel Functions, Excel Training, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, SUMIF and SUMIFS Functions, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: "What-if" Analysis in Excel, Excel Pivot Charts, Excel Pivot Tables, Excel Video Training, Solver Tool in Excel, Video lessons for Excel

Danny September 24, 2012 1 Comment

How to Use Lookup Functions in Excel – Take a Free Lesson from My Latest Video Training Resource

Video Lesson – Lookup Functions in Excel 2007

I have just published the Excel 2007 version of my latest video training resource, “Nine Essential Excel Skills.” And, I want to offer you the opportunity to watch a complete episode. This is one of the 25 video tutorials included on my video training resource. The complete package contains almost four-hours of focused Excel 2007 Training.

Lookup Functions in Excel 2007

Watch this complete 11 minutes and 30 second lesson, as I demonstrate how to use both the VLOOKUP() and HLOOKUP() Functions in Excel. I demonstrate how to return an “exact” match as well as how to return an “approximate” match. I use “plain language” to describe how to use Lookup Functions.

Learn More About My Video Training Resources

Here are the links to the specific product information pages for my latest video training resources:

Download My Step-by-Step Instructional Guide – for Free!

You can download a PDF of my Step-by-Step Instructional Guide for the “9 Essential Excel 2007 Skills” video training resource. I am offering this for free so that you can see the scope and detail of the training that I offer on my 4-hour video. Click on this link to begin the downloading process for my free Instructional Guide.

Watch this Lesson in High Definition

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

Watch Tutorial Now

Share and Enjoy:

Filed Under: Excel Basics, Excel Functions, Excel Training, Lookup Functions in Excel, MS Excel 2007 DVD, Names for Cells and Ranges, Training Tagged: Absolute cell reference, Approximate Match for VLOOKUP, Exact Match for VLOOKUP, Excel Functions, Excel Video Training, HLOOKUP Function in Excel, Lookup Functions in Excel, MS Excel Training, Named Cells and Ranges, Video lessons for Excel, VLOOKUP Function

Danny December 2, 2011 Leave a Comment

How to Return Either an Approximate or Exact Match for VLOOKUP Function

Matches with VLOOKUP

One of my viewers asked me to explain – with examples – how and when to choose either an Approximate or an Exact Match using the VLOOKUP Function in Excel. .

Approximate Match for VLOOKUP

The Approximate Match is the Default setting for Vlookup. This means that you can either omit the 4th, optional, argument or use the word TRUE. However, as you will see in this video tutorial, there are other considerations to consider in order to get the correct result that you are looking for.

Exact Match for VLOOKUP

In the example that I use on the Video Tutorial, I am setting up an Employee Payroll worksheet. I definitely want to have an Exact Match for each employee to ensure that they are getting the correct amount of compensation for the hours that they worked during this period. Watch the tutorial to see how I set this up.

Watch Tutorial in High Definition

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

Get More Excel Tips

I invite you to visit my online shopping website – http://shop.thecompanyrocks.com – to preview the many resources that I offer. Here is the link to go directly to the information page for my DVD-ROM, “The 50 Best TIps for Excel 2007.”

Share and Enjoy:

Filed Under: Excel Training, Lookup Functions in Excel, YouTube Videos by Danny Rocks Tagged: Approximate Match for VLOOKUP, Exact Match for VLOOKUP, Excel Video Training, IF() Function, Lookup Functions in Excel, Named Cells and Ranges, Video lessons for Excel, VLOOKUP Function

Danny November 5, 2011 Leave a Comment

How to Use the Offset Function Inside a 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

Share and Enjoy:

Filed Under: Data Validation, Excel Data Tables and Lists, Excel Training, Lookup Functions in Excel, YouTube Videos by Danny Rocks Tagged: Data Validation, Excel Video Training, Lookup Functions in Excel, Named Ranges in Excel, OFFSET FUnction in Excel, Tables in Excel 2007, Tables in Excel 2010, Video lessons for Excel, VLOOKUP Function

Danny January 1, 2011 Leave a Comment

The 10 Most Watched Videos at The Company Rocks During 2010

Reminder Note

I like to look back to review the results of the past year. Which posts, which videos were the most popular, which videos were downloaded most often? As I look at this list of 10 videos, the clear winner – by category – is merging data from multiple worksheets. Three of the top 10 videos that were watch the most fall into this category!

Here is a listing of the 10 Video Lessons that were watched most frequently on my website – www.thecompanyrocks.com – during 2010:

1- Summarize Multiple Excel Worksheets – Consolidate by Position

2- Build an Accounts Receivable Aging Report

3- Use Pivot Tables to Summarize by Year, Quarter and Month

4- Simplify Data Lookups in Excel

5- Perform Break-Even Analysis with Excel’s Goal Seek Tool

6- How to Display Numbers During a PowerPoint Presentation

7- Make Excel Data Come Alive with Visualization Tools

8- Excel 2003 Basics – Data Entry

9- How to Merge Multiple Excel Workbooks into a Master Budget

10- Consolidate Data from Multiple Excel Worksheets – Part 2 – by Category

If you enjoy these videos, you will enjoy my DVD-ROMs, “The 50 Best Tips for Excel 2007” and “The 50 Best Tips for PowerPoint 2007.” You can use my secure shopping cart to purchase them now.

I have reduced the purchase price of my Individual DVD-ROMs to $29.97 USD. You do not need a coupon to receive this special price. Simply, go to my online store – http://shop.thecompanyrocks.com

You can also watch my videos on iTunes. Click here to go to my Video Podcast, “Danny Rocks Tips and Timesavers” at the iTunes Store.

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

My most recent videos can be viewed in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Share and Enjoy:

Filed Under: Consolidate Excel Data, Excel Basics, Excel Financial Tools, Excel Training, Goal Seek in Excel, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, Powerpoint Techniques Tagged: Consolidate Excel data, Excel Pivot Tables, Excel Video Training, Group Dates in Pivot Tables, Lookup Functions in Excel, Most Viewed Excel Videos, PowerPoint Presentations, Tips and Time Savers, Video lessons for Excel

Danny November 27, 2010 2 Comments

How to Use the Indirect Function to Create Excel Dashboards

You can use the Indirect Function to create an Excel Dashboard. A Dashboard gives you an overview of your data – in this case, an interactive view of the information hidden in our data set. I will create Drop-Down lists using Data Validation in the Dashboard. The Indirect Function takes a little bit of time to understand. Here are some key points:

The Indirect Function works best with Named Cell References
The syntax is =INDIRECT(reference text). In our example the “reference text” will be the label for a Named Range of cells.
You can “nest” the Indirect Function inside another function – e.g. =SUM(INDIRECT())

Later in the lesson, I show you how you can quickly look up any cell at the “intersection” of a Named Row and a Named Column – e.g. April East. You use the SPACE BAR as the “Intersection Operator” to point to the cell where “April” meets “East.”

Excel Tips

Some of the Keyboard Shortcuts that I use in this lesson are:

Ctrl + A to select all of the cells in a Data Set
F3 to bring up a list of all the Named Ranges that have been created in the current workbook
Ctrl + Shift + F3 to create Named Ranges for the cells in the selection – using as Names, the Labels in the Top Row / Left Column

Here is a link to the 1st lesson in this series on the INDIRECT Function in Excel.

Follow this link to watch this lesson in High Definition, Full Screen Mode on YouTube.

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

Share and Enjoy:

How to Use the Indirect Function to Create Excel Dashboards [ 7:20 ] Play Now | Play in Popup | Download (1168)

Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Data Validation, Excel Dashboards, Excel Keyboard Shortcuts, Excel Video Training, Lookup Functions in Excel, Named Cells and Ranges

Danny November 25, 2010 3 Comments

How to Trigger a Unique List of Values Based Upon Another Drop-Down List in Excel

You can use the INDIRECT() Function in Excel to create a drop-down list of unique values that is“triggered” by the label that you select in another drop-down list. In the scenario that I use in this lesson:

Create your 1st Drop-down list using Data Validation in Excel – Allow “List.” Here the named range is “Departments.”
Create a 2nd Drop-down list using Data Validation. This time the “list” uses the INDIRECT Function to reference the selected label from the 1st drop-down list.

Key Concept

The labels in my 1st drop-down list serve as “named range” references. That is why the INDIRECT Function is able to pull up the correct list of “Job Titles” in the 2nd Drop-down list. For example, a reference to the text label “Sales” in the 1st drop-down list brings up a list of the values (Job Titles) from the “Named Range” (Sales) in the 2nd Drop-down list.

If you need to learn about – or review – Data Validation in Excel, follow this link to my free Excel video lesson on this topic.

Create Named Ranges in Excel

I have found that creating and using “Named Cell Range” references makes lookup functions and formulas so much easier in Excel. Watch this video to see how I use a variety of methods to Create Named Ranges in Excel. One tip is to use the Keyboard Shortcut “Ctrl + Shift + F3” – after you first select the Label and the Values for your Named Range.

In this lesson, I also demonstrate how to visually “flag” all of the cells that do not meet the criteria for your Data Validation. I find that most people are unaware that you can do this! I think that you will find many uses for this auditing tool as you work in Excel.

Additional Resources

Follow this link to watch this video on YouTube in High Definition, Full Screen Mode.

Here is the link to view the 2nd video in this series about the INDIRECT Function in Excel.

Learn how to “Master Excel in Minutes – Not Months!” – visit my new, secure shopping site – http://shop.thecompanyrocks.com

Share and Enjoy:

How to Trigger a Unique List of Values Based on Your 1st Drop-Down List [ 6:23 ] Play Now | Play in Popup | Download (1046)

Filed Under: Data Validation, Excel Training, Lookup Functions in Excel, Names for Cells and Ranges, Podcast Tips & Timesavers Tagged: Audit Excel Formulas, Data Validation, Excel Keyboard Shortcuts, Excel Video Training, INDIRECT() Function, Named Cells and Ranges, Named Ranges in Excel, Paste Special, Pick-From Lists, Video lessons for Excel

Danny November 17, 2010 1 Comment

Use the Choose Function in Excel to Quickly Lookup a Value

The CHOOSE Function in Excel is an incredibly useful – albeit, relatively unknown – tool to use when you need to look up a Value in a list. In Excel 2007, you can now use CHOOSE to return up to 254 different Values in a list! (In Excel 2003, the limit is 29 values.)

Here is the Syntax: =CHOOSE(Index_Num, Value1, Value2…) where the Index_Num is a positive serial number between 1 and 254 (In Excel 2007)

In this lesson, I first demonstrate how to look up a “label” (January, February, etc.) for a cell that contains a “Number” for the month (1,2,3, etc).

Rather than struggle with “nested IF()statements,” use the CHOOSE Function when you need to return a value for any number between 1 and 254 – it is so much easier!

Click here to watch this Excel Lesson on YouTube in High Definition Full Screen Mode.

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

Share and Enjoy:

Use the Choose Function in Excel [ 5:56 ] Play Now | Play in Popup | Download (865)

Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Choose Function in Excel, Excel Functions, Excel Video Training, Lookup Functions in Excel, Lookup Values in a List in Excel, Video lessons for Excel

Danny November 15, 2010 Leave a Comment

Use a Combo Box to Dynamically Change Your Excel Chart Data

You can “drive” your Excel Charts – dynamically change the data behind the chart. Focus your audience’s attention on the information that you are discussing. Let your audience see a chart that illustrates the scenario that they select.

To do this – add a Combo Box that lists the choices for each chart display that you offer!

(Click here to view my free Excel Training lesson on Combo Box Controls for more information.)

Key Steps to Take:

Use the INDEX() Function to look-up the values for your chart data.
For the 2nd argument in the INDEX() Function select the cell that is the “cell link” for your Combo Box. Use the F4 Keyboard Shortcut to make this part of the formula “Absolute.”
Insert the Chart Type that best represents your data. Position your chart adjacent to the Combo Box.

Click here to watch this video on my YouTube Channel – DannyRocksExcels – in High Definition and Full Screen Mode.

I have several lessons that cover Charting in Excel 2007 on my DVD, “The 50 Best Tips for Excel 2007.” Buy it now!

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

Share and Enjoy:

Use a Combo Box to Dynamically Change Your Excel Chart Data [ 6:53 ] Play Now | Play in Popup | Download (1407)

Filed Under: Excel Charts, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: Absolute cell reference, Cell Link in Excel Forms, Combo Box Control, Excel Form Controls, Excel Keyboard Shortcuts, Excel Video Training, INDEX Function for Lookups, Lookup Functions in Excel, Video lessons for Excel

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Use the TRIM() Function to Remove Extra Spaces

The #N/A Error Message for VLOOKUP()

Paste Special Values

Essential Excel Skills

Watch Video in High Definition

Top 10 YouTube Excel Videos

Thanks to my 4,500 Subscriber on YouTube!

Learn More About my Extended Length Excel Video Training Resources

Lookup Functions in Excel 2007

Learn More About My Video Training Resources

Download My Step-by-Step Instructional Guide – for Free!

Watch this Lesson in High Definition

Watch Tutorial Now

Approximate Match for VLOOKUP

Exact Match for VLOOKUP

Watch Tutorial in High Definition

Get More Excel Tips

Use the OFFSET Function Inside a VLOOKUP Function

Watch Video in High Definition

Visit My New Secure Online Shopping Website

Click to Play Excel Video Tutorial

Key Concept

Create Named Ranges in Excel

Additional Resources

Key Steps to Take:

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags