A Cool Excel Exercise For Purchasers
PurchTips - Edition # 64
By Charles Dominick, C.P.M., SPSM
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!
Spotlight On Professional Development Opportunities
Did learning about VLOOKUP make you realize that you may not be taking advantage of many of Microsoft Excel's powerful techniques for purchasing? Look, even if you are an experienced Excel user, you may not be utilizing the tools that can help you make sound purchasing decisions and deliver top-notch presentations. Next Level Purchasing's online class "Microsoft Excel For Purchasing Professionals" will enable you to perform sophisticated purchasing analyses through exercises that are based entirely on the situations that you face in your work on a daily basis.
You will learn how to use PivotTables, functions, and subtotals to summarize large amounts of purchasing data, plus...
- How to impress management with professionally formatted bid comparison sheets
- How to assess the competitiveness of supplier pricing through developing
"Should Cost Models"
- How to assess all elements of supplier attractiveness by creating weighted average
- How to use Excel's sophisticated decision support features
- How to visually compare bids, analyze spend, and track supplier performance
by creating charts
- How to develop "Target Pricing Models" for negotiation strategy and more!!!
And if you sign up for this class on or before December 31, 2004, you will get all of these bonuses:
- FREE $10 gift certificate to Amazon.com!
- FREE extended access to the class until March 31, 2005!
- FREE printer-friendly version of the class material!
- FREE audio CD entitled "Career Survival in Supply Management!"
- FREE access to the "Purchaser's Electronic Tools," which includes a negotiation
audio lesson and much more!
For more information on this class, other online classes for purchasing professionals, and the Senior Professional in Supply Management (SPSM) Certification Program, visit:
You don't want to miss out on what might be your last chance to take a FREE purchasing mini-course online, do you? Within the next two weeks, Next Level Purchasing will be making changes to our Web site. These changes will alter how you will qualify to enroll in our FREE mini-courses: "Managing Supplier Performance" and "Internet Reverse Auctions 101."
There is still time to sign up, but not much! While we don't know the actual date we're implementing the change, we guarantee you that if you sign up before December 28, 2004, you will get access to the class of your choice. To sign up, simply complete the following form and fax this page to us in the US at 1-412-299-6635. You'll get instructions by email within 24 hours.