You have to use this function all the flaming time in SEO, for various reasons.
The ‘syntax’ is this:
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.
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 Way Vlookup:
For example, I recently had to get the cost data for each campaign, by hour of the day.
- Concatenate campaign & hour of the day (in this case in column C) in both sheets
- Do vlookup from the first cell in the concatenate column
- The vlookup’s TableArray should now start in Column C (the concatenate column)
2 thoughts on “Excel – VLookup”
I love men