_edited.jpg)
It’s Saturday evening and I’m sitting on the sofa, finally getting around to watching the latest satirical Netflix film, Don’t Look Up. It’s about a pair of scientists who discover a ‘dooms day’ size comet, 6-months and 14 days away from hitting Earth.
What’s this got to do with Microsoft Excel? Well, absolutely nothing… but the title does feature a tenuous link, which has inspired me to write this post!
Microsoft Excel is the software package that just keeps giving. It’s packed full of useful tools to hack your workload, save you time and, if I’m completely honest, make you look like a dark arts practitioner.
One of my favourite tools within Excel is Functions. Functions are the pre-created specialised calculations within the programme, that enable you to do amazing things, like add something up based on a word (COUNTIF) or automatically apply statuses to your data, based on their value (IF Statements).
They also enable you to cross-reference or match two different data sources. These kinds of functions are called LOOKUP or reference functions. I would argue that getting your head around these little powerhouses are essential Excel Skills. So, I’ll take this opportunity to ask you an important question, based on the title of my current viewing pleasure…
Don’t Look Up already? No? Then read on!
In this post, I’m going to talk about the following functions:
If I was going to rank these functions in order of greatness, I’d go for:
To justify this ranking, I suppose I should start with an explanation of VLOOKUP and why, sometimes, it can be a little problematic. Often, students on my Excel training courses agree. VLOOKUP is a slippery devil who has some annoying idiosyncrasies you just must abide by.
Let’s break down the function arguments (or the different pieces of the function that together make it work correctly). The VLOOKUP is made up of four:
Lookup_Value - This is the value you are looking for…
Enter first annoying VLOOKUP demand. This value must be found in the first column of the data you are going to be looking through. So, for example, if the data you are referencing sits in cells B1 to E15, column B must contain the Lookup_value item.
Sometimes this means having to rearrange your tables of data. Not such a biggie, you might argue, but it seems a little ridiculous given how powerful Excel is!
Table_array – Next you must select all the data you want to potentially use in your lookup. Include as a minimum, the first column (in our example, B) and up to and including any data you might want to ‘pull through’ into your VLOOKUP origin spreadsheet.
Column_Index_Number – What the?
Since when did we start to use numbers to describe columns? The second bonkers VLOOKUP rule surfaces. Throw out the lovely letters B to E and replace them with numbers. B=1, C=2, D=3 and E=4.
The number you enter into the VLOOKUP function is the column that contains the information you want to pull through.
Range_lookup – The final one. This is apparently a logical value, except it’s not that logical at all. You can enter TRUE or FALSE in this part of the function to ask the VLOOKUP to check for an exact or an ‘as close as possible' match.
Place your bets on which one is which!
Yup, FALSE means you want an exact match and TRUE, a sort of match. Well, that makes no sense at all!
99.9% of the time, an exact match is what you are going for so if you remember anything, remember that VLOOKUP enters an opposite parallel universe at this point and go with the opposite of what your gut is telling you!
Really, is it any wonder people get a bit confused over a VLOOKUP? All I can say at this point is thank you Microsoft for making XLOOKUP a thing!
XLOOKUP is top of the pile for me because it’s like it's jumped into my outraged VLOOKUP brain and smoothed over all the nonsense. No rearranging of data, no daft column numbering, just simple and logical execution for the win!
To access XLOOKUP, you need to have Office 365, since it really is a bit of a new kid on the block. But if you are umming and erring over whether it's worth making the investment of upgrading, XLOOKUP might just convince you!
You can get XLOOKUP working with just three arguments:
Lookup_Value – Like in a VLOOKUP, but this time you don’t have to worry about where you’ll find this value in your referencing data.
Lookup_array – This can be as simple as just selecting the one column your lookup value can be found in. It can be the very last column of your referencing data, for all XLOOKUP cares. Just tell it and it’ll be kinda chill about the whole fact it’s not the very first column of your array.
Return_Array – throw out the manual counting of columns and the conversion into numbers. All you need to do is select the column that contains the info you want to pull through.
And that’s it. Simplicity.
Check out this Instagram Post on the XLOOKUP. Why not follow me why you are there?
So, if you compare XLOOKUP with the many whims of the VLOOKUP, you will probably end up in my shoes. Since it became available, I’ve said goodbye to its stroppy counterpart and used XLOOKUP in all of my referencing tasks. I can’t see myself going back and I bet, once you’ve tried it out, you won’t either.
This brings me to my least favourite LOOKUP function, HLOOKUP.
I suppose this brings up the rear because, in 12 solid years of using Excel, I’ve probably utilised it once. It’s just as cantankerous as VLOOKUP, which is reason enough, but when you consider that the letter H relates to the arrangement of your referencing data, then you can probably tell why its use is so rare (for me anyway!)
H stands for horizontal, so an HLOOKUP requires referencing data to be arranged with row headers rather than column headers. We are talking about a short and wide data set, rather than a tall and thin list (as used with VLOOKUPS). Pretty sure that every data set I’ve exported from any software package is presented in a way compatible with VLOOKUP, so HLOOKUP is as much use as a chocolate fireguard, in all honestly!
So, there we have it. My random but hopefully informative discussion on the merits (and limitations!) of LOOKUP functions.
Returning to the film, the characters played by Jennifer Lawrence and Leonardo DiCaprio have a hell of a time convincing people that the end of the world is nigh. I’m sitting here wondering if I’ve convinced anyone too, to pick up a computer, open Excel, and try out some LOOKUPs for themselves!
You never know! Maybe I’ll have more success!
Want to be part of The Blue Star Academy?
Then join the exclusive membership site today, for FREE!
Comments