A Cool Excel Exercise For Purchasers


PurchTips edition #64 Click here for the printer-friendly version.| More Purchasing Articles

By Charles Dominick, SPSM, SPSM2, SPSM3

Picture of 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!

Do You Want a More Rewarding Procurement Career?

Are you tired of not getting enough opportunities, respect, and money out of your procurement career? Well, guess what? Nothing will change unless you take action towards becoming a world-class procurement professional.

Earning your SPSM® Certification is the action to take if you want to bring the most modern procurement practices into your organization and achieve your career potential. Download the SPSM Certification Guide today to learn how to get started on your journey to a more rewarding procurement career!


Need Better Performance From Your Procurement Team?

Are you a procurement leader whose team isn't achieving the results you know are possible? Maybe it's not enough cost savings. Or frustrating performance from the supply base. Or dissatisfied internal customers.

You need a performance improvement plan that's easy to implement and quick to produce results. The NLPA can help.

Download our whitepaper "The Procurement Leader's Guide To A More Successful Team." You'll learn the 7 steps for transforming your staff into a results-producing, world-class procurement team.


Are You Getting The Most Out of Your NLPA Membership?

Members of the NLPA get more than just articles like this by email. As a member, you also get access to:

  • The Procurement Training & Certification Starter Kit
  • The latest Purchasing & Supply Management Salaries Report
  • Leading-Edge Supply Management™ magazine
  • Members-only webinars 10x per year
  • And more!

If you haven't been taking advantage of these benefits, why not log in and start now?