Excel – VLookup

You have to use this function all the flaming time in SEO, for various reasons.

The ‘syntax’ is this:

Vlookup syntax
Vlookup Syntax

Here’s an example. I know it seems pretty pointless looking up a price when you could just copy and paste it, but it’s just to show how it works –

The formula is highlighted in the red box.
The code ‘looks’ in cell B3 and finds the code in table which spans G1 to J6, finds the word “Boxing gloves”, then looks horizontally across to the 2nd column, and returns what it finds in there.

Vlookup Example
Vlookup Example

Final thing to look out for.
Vlookup is well annoying because the lookup value (what your looking to match, in this case “boxing gloves”) has to be in column 1, that is, the left most column in your table array.  Look out for this, I still forget and waste time screaming at the monitor.  You can just copy and past columns to arrange a table for your vlookup, or use INDEX & MATCH functions.

If you manage to get a load of n/a errors – it may be because:
1. the cell you are trying to lookup with, doesn’t match anything in the table array.  This can be due to capitalization and/or white space at the end of URLs
2. The column that you are trying to match up to, is not the left most column

My friend has asked me to mention his website about excel – excelinssearch.com
He is a bit lazy tho and hasn’t quite finished the site off as yet

You might use VLookup to check 2 lists of URLs and see which match

I did this today and used the formula =IFERROR(VLOOKUP(A101,$A$2:$A$72,1,FALSE),”not found”)

So I wanted to check the value inside cell A101 was not also in the table (or single column of data in this case) which spanned from A2 to A72

If it was found the URL was returned, if not, then not found was returned.

To ‘lock down’ the look up table, put your cursor on between the A and the 2 in A2 and press F4, do the same for A72 to as well.  This will stop the table array moving down as the initial look up cell moves down on the spreadsheet

2 thoughts on “Excel – VLookup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s