A new function in Excel has rolled out for Office 365 Excel users called "XLOOKUP". This is a feature we've been excited about since its announcement in late summer 2019.
We're excited about an Excel feature? YUP!
This is a feature that will save a lot of time for at least one person in every business. It essentially replaces the function called VLOOKUP which was difficult to train new Excel users on, and even those more experienced would have to line their Excel tables just right to use it effectively.
Here's an attempt to explain why XLOOKUP is so exciting...
The Scenario:
You have a list with thousands of members that you maintain.
You are working with a vendor that also keeps a list of your members, but in their own format
You are tasked with creating a list to see who is missing from their list and who is missing on your list
Since it's 2020, you refuse to manually go one-by-one to review each member. Instead you are going use Excel to help you out.
Now, with Excel and the power of math, there are several ways to accomplish your objective. However, to help me write this blog post, we'll focus on using VLOOKUP and XLOOKUP to solve the problem.
You've already organized you data into an Excel sheet:
Your membership list in Excel
Their membership list in Excel
So, with that lined up, you decide to make your comparison table. Because you know the membership IDs should match on both lists, you copy membership IDs from both lists, and remove duplicates:
Now, there are a lot of different ways and functions you could use to get this information. But this is a blog post intended to show off XLOOKUP, so let's show it off!
When using VLOOKUP, the problem was that you needed to "Count" tables AND the first column in your table.
For "Your Data", that means using column 2 to pull the name from the table:
We see Columns as "A" or "B", or by their name "Membership ID" or "Full Name"
VLOOKUP sees columns as 1 or 2
Column 1 = Column A = "Membership ID"
Column 2 = Column B = "Full Name"
We use column 2 because VLOOKUP counts columns to pull the data
VLOOKUP will not work with "Their Data" without re-arranging the table or using different functions because the Membership ID is not in left-most column:
VLOOKUP uses the left most column
The left most column of "Their Data" is "Their ID"
We need "Membership ID" since that's the number we are looking up
We get a "N/A" error because of this
So what do we do? XLOOKUP to the rescue! It doesn't have this limitation and will figure things out for you!
Using the XLOOKUP function equates to saying: "Hey Excel, please check to see if this <value> exists in this <cell>. If it does, pull the value from <here>. If it doesn't then do <this>."
For the purpose of our comparison list, we want it to say:
"Hey Excel, check to see if this Membership ID exists on this list. If it does, grab the name of the person that has the Membership ID. Then do this for everything on our list."
This ends up pulling exactly what we want:
XLOOKUP saves the day! No data re-arranging. No risks of accidentally tampering the data from copying or moving things around!
From here, you can continue with making your sheet. Since XLOOKUP is going to work without messing with any of the data, you set up your sheet with formatting so it can be used as a template for future comparisons:
Pretty cool, right?
We've worked with numerous people over years to help with scenarios just like this one!
This is just one of many scenarios where XLOOKUP can save you time and help you do your job more efficiently. It's also an example of how having a great IT service provider can help your business modernize and evolve its processes.
IT is no longer about just making sure your computers are working and that you don't have a virus (That's still important!).
It's about designing, implementing, and maintaining a technology infrastructure that enables you, your staff, and your business to take advantage of new features like XLOOKUP.
Interested in having a conversation about technology in your business?
Send in a contact request at www.aevotec.com or email info@aevotec.com.
