PurchTips - Edition # 64
A Cool Excel Exercise For Purchasers
By Charles Dominick, SPSM, SPSM2, SPSM3
Do You Know How To Use VLOOKUP In Your Purchasing Work?
The VLOOKUP function in Excel is used when working with a large, database-style list. It allows you to quickly look up a field value for a certain record. This is best illustrated through an exercise...
Here's the situation: you are a maintenance services buyer for a company with several plants in different cities in the United States. One of your responsibilities is to ensure that the furnaces in each plant are inspected on a regular basis and repaired as the need arises. You keep an Excel spreadsheet listing the type of furnaces you operate in each city, the dates the furnaces were last serviced, the names of the repair suppliers in each city, and the suppliers' telephone numbers. Use the following link to download a spreadsheet to use for our exercise: http://www.NextLevelPurchasing.com/vlookup.xls
When you get a call from a plant reporting a furnace problem, you want to be able to contact the repair supplier quickly. You want to set up the spreadsheet so you just have to type in the name of the city and Excel will tell you the phone number of the repair supplier. You can use Excel's VLOOKUP function to do this. The format of the VLOOKUP function is: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Here's what the labels in parentheses mean...This can get a little confusing, but take your time reading through it and stick with it. It will all make sense when you start typing stuff in a little later.
lookup_value: this is the field value that you know for which you want to find a corresponding field value that you don't know. In our exercise, the field value that you will know will be the city name. Note that you can substitute a cell reference (e.g., B1) for a field value in case you want to do several lookups due to a frequently changing cell value.
table_array: this is the range of cells containing your list. The format for naming ranges in Excel is the upper left cell of your range followed by a colon (:) followed by the lower right cell of your range. In our exercise, the range is A3:E11.
col_index_num: This is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. In our exercise, we want the phone number, which is in the fifth column of our table, so our col_index_num would be 5.
range_lookup: This is a value that will tell Excel to find the exact match or an approximate match. If you want to find an exact match, the value will be "false" (without the quotes). If you can accept finding an approximate match, the value will be "true" or can be omitted. In our case, we want an exact match, so we will choose false for the value.
Now, we want to set up our spreadsheet so that we simply type in the city name in Cell B1 and Excel will populate Cell D1 with the appropriate phone number. So here's what to type in Cell D1 of the spreadsheet you downloaded earlier:
Press the Enter key on your keyboard. "#N/A" will appear in Cell D1. Now, type any city name from the list into Cell B1 of the spreadsheet then press the Enter key on your keyboard and watch as a phone number appears in Cell D1. Type a different city name in Cell B1, press the Enter key and watch as Excel finds the corresponding phone number. Isn't that a cool way of getting the information you need from a list that you have to reference over and over again?
Obviously, this isn't the only way to use VLOOKUP and it is more useful when working with bigger lists. But, think of big lists that you frequently use in your purchasing work. Then experiment with ways to use VLOOKUP to get the information you need quickly. Have fun!
(keep reading for a FREE Offer)
Spotlight On Professional Development Opportunities
Are you tired of not getting enough opportunities, respect, and money out of your purchasing career? Well, guess what? Nothing will change unless you take action towards becoming a world-class purchasing professional.
You see, today's employers refuse to reward employees for yesterday's skills.They demand that purchasing professionals like you use the most modern skills and achieve unprecedented results. They want you to save more money, achieve better operational performance, and reduce risk.
Is lacking a purchasing certification keeping you from getting the results, opportunities, respect, and money that you want out of your career? Today's employers refuse to reward employees for yesterday's skills. They want you to save more money, achieve better operational performance, and reduce risk. They demand that purchasing professionals like you achieve unprecedented results and without the most modern skills that can be a challenging feat!
So how can you acquire the most modern skills, achieve meaningful results, and convincingly prove your capabilities to today's employers? There is an impressive purchasing certification program that will teach you what you need to accomplish all of this and more. It is the SPSM® Certification, earned by completing the Senior Professional in Supply Management® Program. And you could earn it in less than a year - maybe even much less than a year!
Earning your SPSM® Certification by completing the Senior Professional in Supply Management® Program is the action to take if you want to bring the most modern purchasing practices into your organization and achieve your career potential. Learn how to earn your SPSM® Certification (and perhaps get an iPad) (with the option of getting a Study Guide on an iPad) at:
Distinguishing yourself as an achiever in purchasing is key to sustaining a rewarding career, even in these difficult economic times. Earning your SPSM® Certification by completing the Senior Professional in Supply Management® Program is to take if you want to bring the most modern purchasing practices into your organization and achieve your career potential. Learn how to earn your SPSM® Certification (and perhaps get an iPad) at:
Earning your SPSM® Certification by completing the Senior Professional in Supply Management® Program is the action to take if you want to bring the most modern purchasing practices into your organization and achieve your career potential. And if you sign up for the Senior Professional in Supply Management® Program on or before May 31, 2008, SPSMSM Certification Program on or before 4/30/2007, you also get these and other bonuses valued at over $700:
To learn how to earn your SPSM® Certification and get these valuable bonuses, visit:
Earning your Senior Professional in Supply Management® (SPSM) Certification is the action to takeif you want to bring the most modern purchasing practices into your organization and achieve your career potential. Learn how to earn your SPSMSM Certification at:
Learn how to earn your SPSM® Certification at:
The saying goes: "The only constant is change." Nowhere is that statement truer than in procurement and supply chain management - professions that have changed tremendously throughout the years.
Are you prepared for what's next in your chosen profession? Be certain that you are by reading "The Future Direction of Procurement & Supply Chain!"
Download This White Paper For FREE At:
Copyright 2004. This article is the property of Next Level Purchasing and may not be copied or republished in any form without the express written consent of Next Level Purchasing. Click here to request republishing permission.
This is the Web-based version of this article. Click here for the printer-friendly version.
Other Editions of PurchTips:
Free Procurement Training & Certification Starter Kit
This Starter Kit gives procurement professionals like you a clear idea on what it will take to become certified. You'll get an excellent feel for what you will need to learn and how long it will take.
Included in your free Starter Kit:
Together, these materials will help you decide how well the SPSM® Certification can help you achieve your career goals.
So, what are you waiting for? Begin your pursuit of a more rewarding procurement career get your Procurement Training & Certification Starter Kit now by signing up for a free NLPA Basic Membership!
If you are already a member of the NLPA, click here to login and visit the "Starter Kit" tab to access these resources.