Have you heard the news? If you’re not passionate about data like we are at gap intelligence, you might have missed it: Microsoft released a new formula for Excel called ‘XLOOKUP’, the successor to the popular ‘VLOOKUP’ formula, that has many of us spreadsheet-lovers all a twitter.
VLOOKUP Limitations
“What’s the big deal?”, you might ask. Maybe you don’t feel that VLOOKUP needs a face-lift. I myself was not busy writing angry letters to Microsoft demanding improvements to it or it’s sibling HLOOKUP, which I also occasionally employ. I even remember fondly the first time I mastered a VLOOKUP many years ago as a new data associate gapper and couldn’t begin to guess how many thousand times I’ve used it since. However, although useful and a fan favorite among Excel users (it is the 3rd most used formula after SUM and AVERAGE), VLOOKUP does have some irritating limitations. Some that even I didn’t think about until learning about the new XLOOKUP’s improvements. Let’s shine a light on those limitations, shall we?
1. VLOOKUP requires a strict left to right approach of the formula itself, which may require the user to have to manipulate their data to work around this formula.
- Very annoying if the reference column you are using is to the right of the results column you’re looking for
2. VLOOKUP only sends an approximate match rather than an exact match, unless you remember to place ‘FALSE’ in the 4th argument.
- My issue with this is actually the opposite; I’d prefer the ability to not have the lookup only exact-match the reference cell, but this is also addressed by XLOOKUP, which I discuss below
3. VLOOKUP scans from the reference column to the results column, potentially calculating across several columns of data rather than just the 2 columns you may need (assuming you need to use a VLOOKUP over several columns of data).
- If you work in large datasets, this one is a major pain. Anything that can cut down the amount of calc-ing your computer needs to do in a large, formula-heavy excel spreadsheet is crucial
4. VLOOKUP will only return the first occurrence of the search, but not the next, third, or last instance.
- Although not necessarily a common problem I’ve experienced, I could easily find ways to make use of a formula that could identify more than the first instance of an exact match
Even if you never thought any of these were that big of a deal, they are notable limitations nonetheless, and the larger your dataset and more dynamic your data becomes, the more glaring and problematic they become.
The Solution
Enter: XLOOKUP! (*cue triumphant music*), Microsoft’s long, overdue answer to the issues with VLOOKUP. In addition to solving these problems, XLOOKUP will also now absolve the need to use VLOOKUP or HLOOKUP separately as both can be performed within this same function. Additionally, there will be an available option to exact match or exact match minus/plus 1 character, and even ‘wildcard character match’, which can apparently match a range of characters or any characters (not sure the context of using this one but that doesn’t mean I wouldn’t like to try it).
As someone who enters a lot of manual data for the MFP-Copier and Dealer Cost Report services of gap intelligence, I was just as excited as any other spreadsheet junkie after I looked into the new features of XLOOKUP. Finding a new formula that lessens the amount of manual data entry I might have to do or could shorten the calculation time of the large datasets I work in sounds like a dream come true. I’ll admit that I enjoy devising and fixing complicated, loaded formulas to QC thousands of rows of data, but I’m also acutely aware that efficiency is equally as valuable to my workflow. Because of that, I think XLOOKUP could be a real game changer.
One last thing…
Ready for the bad news? XLOOKUP will most likely only be available to Office 365 subscribers, meaning you won’t get to try this new gem of a formula if you’ve only purchased the standard Microsoft Office. According to Microsoft, the new formula is currently only available to users in the Office 365 Insiders Program, and roll-out to other Office 365 subscribers could take several more weeks. With no word on how long the roll-out will take, there’s no mention of the formula ever becoming available to any of us non-Office 365 subscribers. The question now becomes, is it worth it to change over to the Office 365 subscription just to use this formula? I didn’t come here to answer that question (nor do I know), but I will say that Microsoft certainly gave us formula-lovers a great reason to consider it.
For more than 16 years, gap intelligence has served manufacturers and sellers by providing world-class services monitoring, reporting, and analyzing the 4Ps: prices, promotions, placements, and products. Email us at info@gapintelligence.com or call us at 619-574-1100 to learn more.
