Author Archives: Bill Jelen

About Bill Jelen

Author of 41 books about Microsoft Excel.

James Chen: Use Consistent Sheet Names

James Chen sends along this guest post. James is a member of the Excel Gurus group at LinkedIn.

More often than not we need to pull info from various worksheets in a workbook. If you use care to consistently name the worksheets, you can combine VLOOKUP with INDIRECT to pull data from different worksheets. This technique is a life and time saver.

Let’s say you have a sales sheet with various customers and you get a new worksheet every month. Be consistent in choosing a naming rule for the worksheets. For example, you could use Sales_Jan_2012, Sales_Feb_2012, Sales_Mar_2012. This is what I like to call uniform naming.

On a summary sheet, you want to lookup sales for customer XYZ from each monthly sheet. Say that you have true Excel dates for Jan 2012, Feb 2012, Mar 2012 in B3:D3. You have the customer name in A4. The structure of each monthly sales sheet is customer in A and sales in B. Try this formula:

=VLOOKUP($A4,INDIRECT(“Sales_”&TEXT(B$3,”MMM\_YYYY”)&”!$A:$B),2,False)

How it works: the TEXT function formats the date in the format of Jan_2012. The ampersand is a concatenation operator, so you end up passing Sales_Jan_2012!$A:$B to the INDIRECT function. The function will dynamically look at a different worksheet based on the date in row 3.

This is especially useful when you have multiples upon multiples of uniform source data sheets that you need to pull from.


QUE: Using a Helper Row with VLOOKUP

The QUE blog checks in with a VLOOKUP-themed post about using a helper row with VLOOKUP. This technique makes it easier when copying a VLOOKUP formula across several columns.

By the way, my thanks to QUE for donating five door prizes for VLOOKUP Week. If you haven’t entered the daily door prize drawing, visit the Door Prizes link across the top navigation bar.

Read the complete article here.


Rob Collie from PowerPivotPro: Does PowerPivot make VLOOKUP Obsolete?

The first time that I saw PowerPivot create a pivot table from two worksheets WITHOUT doing any VLOOKUPs, I realized that a lot of people who thought VLOOKUP was scary would now be able to do some very powerful analyses. My favorite blog on PowerPivot is run by former Excel Project Manager Rob Collie. This morning, at 12:04AM, Rob checked in with a blog post that offers two amazing techniques:

  1. Joining two tables in PowerPivot using the Create Relationship dialog box
  2. Once you’ve joined two tables, using the DAX function =RELATED in the PowerPivot grid to replace VLOOKUP

Read Rob’s post, VLOOKUP Week – Who Needs VLOOKUPs Anymore.


Excel Blog: You Asked About VLOOKUP

Anneliese Wirth from Office Online provides a round-up of VLOOKUP resources on Office.com and other places. I am just thrilled that Anneliese’s link to “Crazy” lead to a Patsy Cline video and not the one of me in a kayak in front of two fins.

People are crazy for Excel’s VLOOKUP function, and why not? Among other things, VLOOKUP is known for its rugged good looks and unparalleled martial arts ability. In all seriousness, VLOOKUP is one of the most widely used, versatile functions in Excel. Read Complete Article.


Tom Urtis: Case-Sensitive VLOOKUP

VLOOKUP doesn’t care about upper or lower case. If you have ABC and Abc, VLOOKUP will see that as a match. In today’s article for VLOOKUP Week, Excel MVP Tom Urtis shows how to do a case-sensitive lookup. This is a great trick! Read Entire Article

VLOOKUP will never differentiate ATLAS from aTlas. Tom's formula can.


CFO.com article: Handle Growing Lookup Tables with a Two-Key Combo

CFO Magazine offers a solution for growing pivot tables.

…If you need to add new rows to the lookup table, the obvious place is to type the new rows in X8:Z9. However, these rows are outside of the lookup table, so you must re-enter the existing VLOOKUP formulas…. Read complete article at CFO.com


Tom Urtis Explains HLOOKUP

In this introductory-level article, Excel MVP Tom Urtis explains how to use HLOOKUP.

HLOOKUP is a function not as easily grasped nor widely used as its VLOOKUP counterpart, probably for good reason as most lists are vertical in nature. The picture shows an example of using the HLOOKUP function to find a lookup value (“Mar” in this case) along row 1, hence the “H” in "HLOOKUP" referring to a horizontal lookup action.  Read complete article at AtlasPM.com