Success!

An email was just sent to confirm your subscription. Please find the email now and click 'Confirm Follow' to start subscribing.

Oops!

You have already subscribed to this site. Please check your inbox.
You can manage your preferences at subscribe.wordpress.com

Excel VLOOKUP and HLOOKUP (Part 5 of 5: Essential Excel Skills Every Controller Should Know)

November 26, 2019 | New View Strategies

It’s been a while since I’ve talked about Excel functionality. As an accountant, we spend a lot of time in Excel and today we’re going to explore the VLOOKUP and HLOOKUP functions. The VLOOKUP function is used when you need to find things in a table or range by row. Similarly, HLOOKUP is used when you need to find things in a table or range by column.

As a Dynamics NAV / BC user, we often find ourselves having to cross-reference data from one table to another and having IT help us every time just isn’t sustainable. Learning to use additional Excel functionality can really enhance our ability to analyze data and provide reporting.

Imagine wanting to create a pie chart that shows, by Customer Name, outstanding amounts from the Customer Ledger Entry table of BC. Below, you can see that the Customer No. is available in the table, not the Customer Name.

 

 

To accomplish this, I’ve brought the Customer Ledger Entries and Customer tables into a single Excel document. These functions do also work across workbooks and tabs.

 

 

There are 3 data points needed to put together this formula:

  1. The value to look up. In this example, I want to look up the Customer No.
  2. The range to look up. The table with Customer No. and Name is my range.
  3. The column number of data to return. Since I’m looking for the Customer Name, I’ll need to return column 2.

 

The formula looks like this: =VLOOKUP(E3,$J$3:$K$7,2, FALSE). Don’t forget to put in the absolute reference on the range ($) to ensure the lookup range is locked.

 

 

Let’s use the same example with a slight twist to see how the HLOOKUP works. The formula looks very similar =HLOOKUP(E6,$C$2:$G$3,2,FALSE).

 

 

In both cases, I now have a table I can create a pivot table from and build the desired pie chart.

 

 

Getting comfortable with these two formulas can enhance your use of Excel, to create a new level of reporting in your business.

 

Check out the rest of this series, Essential Excel Skills Every Controller Should Know:

  1. Excel Workbook Sharing
  2. Conditional Formatting in Excel
  3. Sparklines & Quick Analysis in Excel
  4. Excel Goal Seek
Subscribe to our RSS Feed

Subscribe to Blog Updates

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 66 other subscribers

You can unsubscribe at any time. To view our Privacy Policy click here.

The experts at New View Strategies are experienced Dynamics NAV and Business Central trainers and business process consultants who have walked in your shoes as former Corporate Controllers, Directors of IT, and Solution Architects, and believe increased utilization of your business systems is critical to the continued success of your company.
SUBSCRIBE TO OUR NEWSLETTER
Subscribe to our newsletter and keep up to date with classes, special registration discounts, and other NVS news

You can unsubscribe at any time. To view our Privacy Policy click here.

| Website Proudly Designed by Phoenix3 Marketing
Call Us!
cross
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram
%d bloggers like this: