Excel VLOOKUP: Transform Your Product Descriptions from Ugly Ducklings to Beautiful Swans

For many years, I used Excel and never knew about VLOOKUP. Oh, the hours I wasted! I’m writing this post to save you from the same sad, productivity-sapping fate.

What is VLOOKUP? It’s a simple Excel function that allows you to pull information from one data set to another, based on some matching value. While that may not sound too exciting, I predict VLOOKUP will become one of your Excel BFFs (Best Functions Forever). Why? Because VLOOKUP allows you to avoid manually entering the same information into your IRI/Nielsen downloads and reports over and over again.

Today I’ll illustrate how to use VLOOKUP with your CPG data in one common application: transforming ugly duckling UPC descriptions into beautiful swans in every report with a one-time setup and simple formula.

Using VLOOKUP to Transform Product Descriptions

You’ve probably all seen cryptic and unattractive UPC (Universal Product Code) descriptions.  Here are a few I’ve pulled from my files:

ALBA HWN PASN FRT .15Z
GRMTN CAR MRNG BLEND KCUP (12 EA)
PVL ORG TOFU FIRM OGC 14 OZ
DOLE GRL CSR SLD KT DRS 9.9OZ

These examples come from IRI, Nielsen, Kroger, and SPINS (SPINS supplies syndicated retail information on natural and organic products). Can you tell which description came from which source? Probably not, so you can see it’s a problem you will run into with most CPG data.

While these descriptions are technically accurate, they’re not optimal for your audience. You can translate them into plain English, of course, but how to avoid manually fixing them month after month, analysis after analysis? VLOOKUP! With VLOOKUP, you can clean up descriptions once and then add them to any future dataset, matching on UPC number.

Let me show you how to do this in more detail with an example.

Here’s my raw data, displayed exactly as it came from Nielsen/IRI:

As you can see, the product descriptions in Column C are long and hard to read. In addition, not all the abbreviations are self explanatory.  Rather than updating the product descriptions manually, I create a lookup table with audience-friendly product descriptions.  The original descriptions are in column C and my new, improved descriptions are in column D:

You may have noticed that the order of the items in my lookup table is different than the raw data table.  That’s OK.  I also have items in the lookup table that don’t appear in the raw data.  Also OK. One important requirement, though, is that the first column in the lookup table must be the variable used for matching (in this example, that’s UPC).  The matching variable can appear in any column data table but must be the first column in the lookup table.

Using a formula, which I will explain in detail below, I match the values in the raw data table to the corresponding values in the lookup table using UPC as the link:

And voila! Now you have a table with product descriptions that your reporting audience can understand. And you can use these same descriptions month after month with little additional effort.

Now let’s take a closer look at the VLOOKUP formula used in the example:

Argument 1: The cell address for the raw data value you want match to the lookup table. In this example, that’s UPC.

Argument 2: The range that contains the lookup values. Generally, you should make this an absolute reference using “$” signs because you don’t want the lookup range to change when you copy the formula down a column.

Where should you store your lookup data? Technically, you can store it anywhere. In this example, we put the lookup data in a separate sheet in the same file as the raw data. However, you can also put the lookup information right in the raw data sheet or in a completely separate file. The best place to store your lookup data will depend on how much lookup data you have and if/how you plan to use the lookup table in the future.

Note that, in this example, the sheet name (“UPC Lookup Table”) appears in the cell reference because the lookup table is stored in a separate sheet in the same workbook. If the lookup information is stored in a separate file, the cell reference must also include the file name.

If you think you might add more values to your lookup table as new UPCs come on the market, then it’s a good idea to include blank rows in your lookup range. This will save you from having to adjust the VLOOKUP formula each time you add new rows to accommodate new products.

Argument 3: The column number in the lookup range that includes the data you want to display. In this example, I’m pulling in New Description values from Column D. I specified my lookup range to include Columns B through D. Therefore, Column D is the third column in the lookup range. So for this argument, I’ve specified “3.”

Argument 4:  I always specify “FALSE.” “FALSE” tells Excel you want to exactly match the data table value and the lookup table value. “TRUE” would allow an approximate match. If you want to know more about this argument, and when you might want to use “TRUE” for this argument, refer to your Excel documentation.

If you’d like to convert your ugly duckling product descriptions into graceful swans, and you’d like a copy of my example Excel spreadsheet to get started, click here to download the file.  As a bonus, I’ve expanded the example to include several different lookups and a pivot table. If you are having trouble with the download, contact me and I can email you the file.

Need help customizing your Nielsen/IRI reports? Contact us to discuss your project. We offer services targeting all phases of your reporting needs: specification, design, and programming.

Did you find this article useful? Subscribe to CPG Data Tip Sheet to get future posts delivered to your email in-box. We publish articles twice a month. We will not share your email address with anyone.