The Excel Habit That Is Quietly Costing Your Team Hours (And Giving You Wrong Numbers)
10 Aug 2026
Published in: Member News
Most Excel users were never formally trained, and one twenty-year-old habit is still producing plausible-looking but incorrect figures in businesses across the Black Country. Here is the fix, and it takes ten minutes.
Ask around your office how many people have had formal Excel training. In most Black Country businesses the honest answer is very few. People learn Excel from whoever sat next to them, from a colleague's old spreadsheet, or from a search engine at four in the afternoon when something needs to go out.
That works, up to a point. The problem is that self-taught habits get passed down unchanged, and one in particular is still causing businesses to make decisions on numbers that are quietly wrong.
The culprit: VLOOKUP
If anyone in your business pulls data from one table into another, matching a product code to a price, an employee number to a department, a postcode to a delivery zone, they are almost certainly using a function called VLOOKUP. It has been the standard way to do this for over twenty years.
VLOOKUP has a flaw that catches people out constantly. If you do not explicitly tell it to find an exact match, it looks for the closest approximate match instead, and returns that. It does not throw an error. It does not warn you. It returns a perfectly reasonable-looking number that happens to be the wrong one.
It has a second problem too. VLOOKUP works by counting columns from the left, so if anyone inserts a column into your spreadsheet, every formula silently starts pulling from the wrong field. Nothing breaks visibly. The numbers just change.
The fix: XLOOKUP
Microsoft solved both problems by replacing VLOOKUP with a newer function called XLOOKUP. Most businesses already have it, since it is included in Microsoft 365 and in Excel 2021 and 2024, but comparatively few people have made the switch simply because nobody told them to.
XLOOKUP finds an exact match by default, which removes the first problem entirely. And instead of counting columns, you point it directly at the column you want the answer from, which removes the second.
The basic version takes three pieces of information: what you are looking for, where to look for it, and where to get the answer from.
=XLOOKUP(G2, B2:B100, D2:D100)
Read that as: take the value in cell G2, find it somewhere in column B, and give me the matching value from column D. That is the whole thing. No column counting, and no risk of an approximate match sneaking through.
There is one genuinely useful extra. Adding a fourth item tells Excel what to display when nothing matches, which is far friendlier than an error message:
=XLOOKUP(G2, B2:B100, D2:D100, "Not found")
Three things worth knowing before you switch
A few practical points that save a wasted afternoon:
- Check your Excel version first. XLOOKUP works in Microsoft 365, Excel for the web, Excel 2021 and Excel 2024. It does not exist in Excel 2016 or 2019, so if you share workbooks with a client or supplier on an older version, the formula will show an error on their screen even though it works perfectly on yours.
- You do not need to rewrite everything. Existing VLOOKUP formulas that produce correct results are not urgent. Use XLOOKUP for new work and convert older ones as you happen to touch them.
- Make sure your ranges are the same size. If you search 100 rows but return from 50, Excel will complain. It is the most common error people hit, and the easiest to fix.
If you would like the fuller version, our complete guide to XLOOKUP covers how to match on two conditions at once and how to pull back several columns with one formula, with worked examples you can copy straight into a spreadsheet.
The wider point
XLOOKUP is worth ten minutes of anyone's time, but the real issue it exposes is bigger. When a whole team is self-taught, everyone uses the functions they happened to learn first rather than the ones that would save them time. Nobody knows what they are missing, because nobody has seen the alternative.
That is usually where the hours go. Not in one dramatic error, but in a hundred small workarounds that each seemed reasonable at the time, built by people doing their best with what they were shown. A few hours of structured Excel training tends to surface a surprising number of them at once.
This article was contributed by Vantage 365, a Microsoft Solutions Partner based in Brierley Hill working with businesses across the Black Country. If your team's Excel skills were picked up rather than taught, our hands-on Excel training courses run at foundation, intermediate and advanced level, and our full guide to XLOOKUP is free to read. You can find out more at vantage365.com.
Comments
Post A Comment
You must be logged in to post a comment. Please click here to login.
Our Patrons
