The Edit Blog

The dangers of using Excel for SEO

ARTICLE BY Alan Ng
READ TIME: 1 min
13th August 2012

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!

  • By pressing submit you consent for us to process your data in order to answer your query, according to our Privacy Policy.

  • This field is for validation purposes and should be left unchanged.

Part of the Kin and Carta plc

© 2018 Edit. Kin and Carta plc. Company reg. no. 3624881, All rights reserved. VAT Registered GB 927458295 Privacy Policy | Terms & Conditions | Cookie Policy