Excel's VLOOKUP and Substitute Functions

Love it or hate it, Excel probably consumes the lion’s share of our days as web analysts and search marketing professionals. This is the first of a series designed to explore some common uses of excel overlaid with some internet marketing data tasks.

One of the functions I use most often in excel is VLOOKUP. If you’re familiar with SQL, you can think of Excel’s VLOOKUP as a poor man’s LEFT JOIN. If you’re not familiar with SQL, however, please don’t think of it this way.

The basic idea behind VLOOKUP is to match up two lists of data based on a common value. In the context of web analytics, it’s likely that I have two separate lists of of URLs I’m trying to join. Perhaps one list came from my logfiles, and another from my tag-based web analytics tool. To create a comprehensive view of each URL (append the log data to the tag data for each URL), I can combine them using Excel’s VLOOKUP.

The VLOOKUP syntax goes like this:

VLOOKUP(key, data range, column number, partial match?)

Excel will look for the Key value (our Request URI) in the logfile data block, and return the 2nd column value.

• Here, the key is the Request URI from the tag-based list.
• The data range is the entire block of data from the logfile data, including the column we want to match against.
• The column number is 2, b/c ‘hits’ is the second column in our data range.
• Partial match is ‘FALSE’

If our list of URLs matched against our list of request URIs, we’d be done. Copy the formula down, adjust the column lookup from 2 to 3 for ‘Last Accessed’, and call it a day. Unfortunately, our data isn’t that clean, and entering the formula as-is will create an error. We need to strip out the domain information from our URL path so that there’s a clean match between those URLs in the two lists. One option is to use Excel’s substitute function.

The SUBSTITUTE function syntax looks like:

SUBSTITUTE(text/cell, Old text, new text).

Again, if it’s useful to you, think of this like PHP’s str_replace or SQL’s REPLACE.

• Text is a cell reference to the URL from logfile data
• Old Text is “www.mysite.com”
• New Text is “”

Essentially, we’d like to replace the www.mysite.com with nothing so it matches the values in the list above, which we’ve done in this screenshot.

Putting these both together allows us to append the data from our logfile list to our tag-data list.

Here’s the final spreadsheet showing formulas.

Troubleshooting VLOOKUP

VLOOKUP is a pain in the ass for a couple reasons. Your values appear to match, but excel keeps giving you its version of the middle finger, the #N/A. Here are a couple reasons why this might happen.

• Your key or lookup cells have trailing spaces.

If this is the case, use the TRIM function to remove trailing spaces from both of your lists.

• Your key or lookup cells is a text, but looks like a number.

If this is the case, use the VALUE function to convert the text to a number.

Related posts:

1. Text functions: mid
2. Text to Columns for URLs
3. Excel's IF Function & Categorization
4. How Can I Use Google Analytics to Increase Traffic to my Website?
5. Keyword Categorization with Wordle

Tagged as: analysis, excel, Web Analytics