The dangers of using Excel for SEO

Excel is the staple data processing tool for any technical SEOer, and has been for a long time.

While Google Docs may have stolen some of the limelight with their importXML and XPATH web scraping capabilities, if you’re doing any serious data processing then you’ll most likely turn to the trusty Microsoft stalwart.

Amongst the many SEO-friendly features of Microsoft Excel comes the VLOOKUP function. What this function does shouldn’t need any explanation; if it does it probably means you’re not going to enjoy the rest of this post!

With the recent spate of Google Webmaster messages about unnatural links and the whole web scrambling to clean up their backlinks, data and information management has never been more important. Cross referencing data that is tens of thousands of rows long, from multiple sheets and multiple columns is no easy feat. Thankfully, there is the VLOOKUP function to rely on and make our lives easier…or so we thought.

Using VLOOKUP on URLs without really understanding how it works can mean frustration and even disaster.

Take this scenario: you’re cross referencing URLs, one of which looks like this:

http://www.this_is_a_fictional.com/~url/%34locate

If you perform a VLOOKUP on this URL it will fail with the dreaded #N/A.

The Reason:

The Tilde (~) is a special character in Excel and needs to be replaced with its string form to exact a match by incorporating the SUBSTITUTE function.

The Conclusion

This entry will fail on certain link URLs:

VLOOKUP([lookup_value],[lookup_range], [column_index],[match_type])

If you’re doing URL look ups use this safer form instead:

VLOOKUP(SUBSTITUTE([lookup_value],”~”,”~~”),[lookup_range], [column_index],[match_type])

It’s a bit function heavy, so to make it easier make a custom function to use with the workbook. Please see how we do this by downloading the example workbook.

We hope this helps everyone out there stay Excel safe!

BY Alan Ng AT 11:55am ON Monday, 13 August 2012

With a background in applied optical engineering, Alan found his passion for Search and Analytics just a few years ago. With his extensive mathematical knowledge and enthusiasm for search tools, Excel and refining processes; Alan is part of our Data Insights Team. Follow @Typeaccord on Twitter