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?).
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).
In essence, when these formulas are amalgamated, the end result looks something like this:
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.
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.
