Tuesday's Tip #30: Impress people with the New XLOOKUP Function

excel Jul 28, 2020

Have you used the New XLOOKUP Function?

Now - if you have used VLOOKUP (or HLOOKUP) you will appreciate the XLOOKUP function. Fair Warning: if you're not particularly versed in functions and formulas - this tip might get a little techy BUT I'd encourage you to watch the XXX minute video anyway because you may pick up a new skill or plant a seed for something down the road OR MAYBE you can just throw this around when chatting with your boss or client and REALLY impress them!!

When would I use the XLOOKUP Function?

The XLOOKUP function can be used anytime you previously used the VLOOKUP or HLOOKUP function. BUT it has some great time-saving features built in. If you're familiar with the VLOOKUP (or less common the HLOOKUP) you will REALLY appreciate these.

In general, lookup functions are for referencing data in other data sets. For example, you are wanting to add employee numbers to a table you're working on but you have that information in another list. A LOOKUP function allows you to reference that list automatically.

Top 3 Things I Love about the XLOOKUP Function

Exact Match is the default.
You no longer have to enter in an argument to make it an exact match. THANK GOODNESS!! Almost everyone I have ever taught the VLOOKUP function to wanted an exact match and I never could understand why the default wasn't exact match!
Lookup data to the Left of the lookup value.
Again, such a simple thing but if you were using a table that was already setup and the lookup value (the common value) didn't happen to be the left most column, you either had to rearrange your table or copy that column or make some kind of work around. THIS IS HUGE when working with tables that already existed. SO AWESOME!
Extract Multiple Values with one function.
It used to be that if there were multiple columns of information you were interested in looking up, you had to create multiple functions. You had to create and copy the function into a second and third (or however many) column to get the additional information. NOW you just change the range and it outputs as many columns as you include. AGAIN - a HUGE TIME SAVER!

There are some other cool things too, here are a few.

Replace Index/Match combo because of these enhancements
Allows for vertical and horizontal arrays
Allows SUM of multiple XLOOKUP functions

Specific Steps on How to Use the XLOOKUP function