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 [download id=”8″ format=”3″].
We hope this helps everyone out there stay Excel safe!