The dangers of using Excel for SEO

Author avatar

Alan Ng

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!

Bath

+44 (0) 1225 480 480

20 Manvers Street

Bath

BA1 1JW

Leeds

+44 (0) 113 260 4010

2nd floor, 2180 Century Way,

Thorpe Park,

Leeds, LS15 8ZB.

London

+44 (0) 113 260 4010

5th Floor, Cordy House,

91 Curtain Road

London, EC2A 3BS

Part of the St. Ives Group

  • By pressing submit you consent for Edit to contact you via your email or telephone number for purposes relevant to your request for our goods or services. Your contact details, including your name, company, telephone number and email address will be used by Edit. By contacting you are agreeing to Edit’s Privacy Policy. If you have any questions, please ensure you review this section before submitting.

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

© 2018 Edit. St Ives Group. Company reg. no. 3624881, All rights reserved. VAT Registered GB 927458295 Privacy Policy | Terms & Conditions | Cookie Policy