Did you know that your browser is out-of-date?

To get the best possible experience using our website we recommend that you upgrade to a newer version or other web browser. A list of the most popular Web browsers can be found below.

Just click on the icons to get to the download page.

I don't want to get the best experience continue without upgrade
navbar toggle
search toggle

How Excel's VLOOKUP & IFERROR Can Save you Hours

May 22, 2014
0 Comments
VLOOKUP. IFERROR. Two formulas I could not understand separately, let alone when they were conjoined. It took a lot of time, practice and frustration before I got them right. A lot of obstacles were in my way: Excel would freeze and/or crash, an urgent deadline would come up or I’d find an excuse not to do it (need dishes washed, anyone?). 
  Prove Excel Formulas 
To start at the beginning, a VLOOKUP formula is used when you want to find a value in the first column of a table range, and it returns a value in another column in the table array. An IFERROR formula, on the other hand, returns a value you specify if a formula calculates an error; otherwise, it returns the result of the formula. If the above doesn't make any sense, you’re probably looking like this right about now (and I did too, at one stage).
Spock

In essence, when these formulas are amalgamated, the end result looks something like this:


Formula-Graph-1


The reason I had to learn how to use this formula is because I used to spend hours trying to manually cross-reference and check multiple sheets of data in an Excel document, and I’d often find errors which would force me to start from scratch. This would not only waste my time, but exasperate me to no end. In this industry, being proactive and self-reliant are qualities you need to possess. Waiting for someone else to help you usually means you will wait for ages. Not only are we all incredibly busy, but the feeling of accomplishment you get from teaching yourself something is immense. The perfect opportunity to test my Excel formula knowledge (and endurance) presented itself when I had two lists of URLs to check against each other. One was an old list which was compiled a while ago; the other was a recently updated version. Finding out which URLs from the old list appear in the new list and vice versa would have taken forever if done manually. So I bit the bullet and attempted to master the art of combining VLOOKUP and IFERROR. The end result looked something like the following:  I had both URL lists in their respective sheets, and in cell B2 of each sheet, I inserted the IFERROR/VLOOKUP combo. At this point I added in the text I wanted to have appear if the desired result didn’t present itself (in this case, I’ve specified it to say “not in new sheet” on the “Old URLs” sheet, and “not in old sheet” on the “New URLs” sheet).From these I was able to see which of the URLs from both sheets did not correspond. Below is what my “New URLs” sheet looked like once I cross-referenced the new list of URLs against the old list. You can see the formula as it appears from row B2 down, and that 4 of the URLs from the new list are not in the old list.
  Iferror Vlookup_New URLs Tab
It took a few attempts before I got this to work. When I did get it right though, I realised these formulas are like a wolf in sheep’s clothing … in reverse. They might seem tough and scary, but actually are quite easy to master.   What did I learn from this experience (other than the obvious)? I learnt that patience can stretch quite far. I also learnt that sometimes when something seems really difficult it actually isn’t and that Excel formulas in general are less scary and more time-efficient (except the one below… that one is the exception. Good luck with that one).     If you're able to write this out in English drop us a comment below to show off.
Did we say that we would love to work with you?
So please don't be shy, get in touch!

Kennington Park, Unit LH 301 , 1-3 Brixton Rd
London SW9 6DE
+44 (0)203 735 9592 info@the-media-image.com