|
|
Today’s post is brought to you by Anneliese Wirth, a writer on Office.com. |
On Office.com, the term “VLOOKUP” frequently bubbles up in our list of high-volume search queries. My job is to figure out why this is and what specific information people are actually looking for when they type “VLOOKUP” into the search box in Help or on our web site.
The “VLOOKUP” query appears to represent a bunch of different questions. Certainly, one of the main ones has to do with the function arguments—a.k.a., “the stuff between the parentheses.” The bottom line is this: If you don’t use VLOOKUP regularly, it’s hard to remember off the top of your head what the different arguments mean, or how to use them correctly.
From personal experience, I can really relate. I use VLOOKUP infrequently, usually when I need to run a particular type of report for my writing team. The report requires me to pull data from my content management system, and then match topic ID numbers in one worksheet to corresponding titles in another worksheet. To make things work, I invariably end up in Help, poring over our VLOOKUP topic.
In particular, as an occasional user of VLOOKUP, I wonder about the following:
· What pieces of the formula do I need, and in what order?
· What’s the secret handshake for referencing lookup tables on other sheets?
· Is TRUE the right argument for exact matches? Or is it FALSE? Which one is the default? Which is the one that requires me to sort the first column in the lookup table in A to Z order?
· When counting columns, do I start at 0 or at 1?
· Why am I seeing the wrong results when I copy the formula?
To help address questions like these, I created a dedicated VLOOKUP Refresher. Feel free to print a copy of this card or save it for later, whenever you need it.
Of course, anyone who uses VLOOKUP will eventually want to know THIS, too:
· Why am I getting #N/A errors?
That last bullet is a whole ‘nuther ball of wax. I’m currently working on a card of troubleshooting tips and techniques for circumventing #N/A, but in the meantime, check out Greg Truby’s recent post, Solutions to three common problems when using VLOOKUP().
If you have comments about the VLOOKUP Refresher card, tips or resources of your own, or suggestions for future posts, please leave a comment.
http://www.TeachMsOffice.com
Go to http://www.teachmsoffice.com for this and more tutorials.
Visit http://www.TeachExcel.com for more, including Excel Consulting, Macros, and Tutorials.
Macro vba in Excel for automatic calculation of the MRP, explosion of the bill of materials and necessities of purchase, excellent macro to make the management and planning of the company.You can download in www.webandmacros.net
Wall St. Training Self-Study Instructor, Hamilton Lin, CFA covers the creation of macros (via record macro), how to create a macro button and toolbar as well as assigning the macro to the button, customizing the image of the macro icon and always having it at your disposal. Unfortunately, Excel 2007 significantly reduces the functionality of macros and toolbars and the new Excel for Mac completely removes VBA support for Excel. Go figure, great job Microsoft.
More advanced version of excel
Automate your calculations in Excel using Macros. You need to have a knowledge of VBA (Visual Basic for Applications). VBA for Excel is easy to learn.
Visit http://www.TeachExcel.com for more, including Excel Consulting, Macros, and Tutorials.
Prompt user for a username and password when a workbook is open. Lots of uses for this.
