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:
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.
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.
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.
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 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
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