Looking Back at 2020: Microsoft Biggest Gift to Excel Users
2020 will go down as an unforgettable year. Unfortunately, for most of us, it will be unforgettable for the wrong reasons.
Microsoft made this year a little better by gifting us with two new functions in Excel that have quietly made our lives so much easier! The functions I’m talking about are XLOOKUP and UNIQUE.
If you are using Excel to work with large sets of data, you are surely using lookup functions. Look up functions are so useful that many employers will put it as a requirement right on a job description.
The most common lookup function is a VLOOKUP. It takes a target value and compares that value against values in a lookup column. When a target value hits a match, it will return a value from a corresponding column that you have specified.
VLOOKUP has its limits. The most annoying nuisance of a VLOOKUP is that column from which you are extracting the value must be to the right of the lookup column. Not to mention you need to know column number of the column you are extracting the value from. Imagine having a table in the middle of a spreadsheet and your lookup and return value columns are tens of columns apart. What if the values you are attempting to extract are to the left of the lookup column? Then the whole table needs to be reorganized for VLOOKUP to work.
For all reasons listed above, many professionals began using INDEX-MATCH nesting formulas. INDEX is a function that returns a value in the specified cell. Inside INDEX formula you can write reference to a row or column as another formula. That is why MATCH formula is used. MATCH will return row number when it finds a target value in a lookup column. INDEX-MATCH is flexible and solves VLOOKUP shortcomings. By using this method, you do not need to know column numbers and you do not have to have extraction column to the left of the lookup column.
While INDEX-MATCH is flexible, it is hard to read. After all, you are nesting one formula into another. It is also complex to enter if you are pressed for time. Microsoft decided to reward all of us by introducing XLOOKUP function. The function is amazing! All you have to do is enter what you are trying to find, where you are trying to find it and where to look for corresponding results. That’s it! That simple!
In addition to syntax simplicity, XLOOKUP allows you to start search from the bottom of the list. This is important, because with VLOOKUP or INDEX-MATCH you would have to sort an entire table yourself.
What else it does you might ask? Remember how your spreadsheet breaks because lookup function did not find anything and threw an error? Remember how you had to type in IFERROR formula to force a correction when that error occurred? Well… great news! XLOOKUP comes with an optional argument that will take instructions of what to do if there is an error.
This function is a best kept secret. I find my colleagues do not get too excited about this function. I guess this is because you will deeply appreciate it if you had to build interactive spreadsheets within Excel. While the function is simple, it massively improves flexibility of Excel.
Let’s say you are building a dashboard in Excel that will pull from different sources depending which value is selected on a dashboard. For example, imagine a dashboard allows you to select from a list of products. Different serials numbers will be attributed to each product and those serial numbers can be used to lookup information that we want to appear on the dashboard.
In old days, you would have to maintain two lists in a table range that could be selected using data validation. Sounds confusing? Try building it!
UNIQUE function simplifies that process by having an ability to be fed different lists dynamically. Unique function will then present the results by summarizing values only once in a single column that can be used by lookup functions. The function is amazingly simple, but applications are immense!
