• Mark Monfort

The most powerful function in Excel... and a new LOOKUP

Whether you're into data analytics, advanced analytics or data science, no doubt you'll have been using Excel (or you're still using it). Mind you, there's nothing wrong with this. In fact, it's a great, easy to use and multi-faceted tool that gets most data and spreadsheeting jobs done.


However, the gripes about Excel are what push people into using business intelligence or data wrangling tools like Tableau, Qlik, Alteryx or Knime. We often see tasks in Excel that are repeated each month such as the cleaning an analyst has to do with dirty data. In Excel, there are various ways this can be handled but they can get messy with the addition of formulas in hidden columns etc. With the advent of the new LAMBDA function coming out in December 2020, say goodbye to clunkiness with repeated tasks and hello to our friend, recursion (see explanation here: https://en.wikipedia.org/wiki/Recursion_(computer_science).


We'll explain more on LAMBDA below as well as a new way of doing lookups. Move over VLOOKUP and HLOOKUP, here comes XLOOKUP.


LAMBDA

There is a great video that explains this here:


Whilst the video starts off with some simple mathematical examples of functions you can create, it ramps it up with some data cleaning techniques commonly referred to as regex which stands for regular expressions (https://en.wikipedia.org/wiki/Regular_expression). These are used in the data cleansing world to remove things like unwanted characters, spaces and the like from text and if you've ever dealt with dirty data you'll know how much this is needed.


Normally, you would need to create a number of different columns to handle each change and then return each result. With LAMBDA, you can set recursion to work. The example starts at 5:57 of the video where we see a Microsoft post about this on their Tech Community blog.



Then we go into an example of the function being used in Excel

The great thing is that you can easily test this out and change it with some of your own data since the video is so easy to follow along.


XLOOKUP

XLOOKUP is not as new as the LAMBDA function but it did just come out in 2019. Again, we'll take a look at a video that showcases this in action and break it down.

The most popular of lookup functions has to be VLOOKUP given how often it is used compared to HLOOKUP but it has some limitations. For example, if there are multiple matching results that are in the lookup table, you will only get the first result.


XLOOKUP is different. It starts out the same with the first argument being the value you want to lookup and the next being the array to look into, but then it changes. Normally, you would specify a number of columns to look into for a return value and that would be to the right of the lookup table. It means you need to sort your lookup table first. This can take time and it might not be easily possible. With XLOOKUP, the 3rd argument is the row values themselves and this means it can be to the left or the right. That's pretty powerful.


Additionally, XLOOKUP has other argument options as shown in the video where the next highest value can be looked up in the case of bonus percentages. If someone is earning $62,000 and the bonus at $60,000 is 10%, normally there would need to be an exact match first. With XLOOKUP it can find the next lowest salary to $62,000 and assign that bonus percentage (as per the video). Powerful stuff!


There are tonnes of great videos like this out online and they are definitely some of my favourite functions I've tried recently.


So with LAMBDA and XLOOKUP in your toolkit, what will you solve today?

93 views0 comments

Recent Posts

See All