PERTH consultant Wyn Hopkins has a special claim to fame; he prompted Microsoft to update its highly popular Excel package to fix an issue that had been annoying users for years.
PERTH consultant Wyn Hopkins has a special claim to fame; he prompted Microsoft to update its highly popular Excel package to fix an issue that had been annoying users for years.
A Microsoft specialist and the director of Perth-based Access Analytic, Mr Hopkins posted on the Microsoft Excel feedback forum User Voice in May 2016, highlighting problems with VLOOKUP function and offering a solution.
Mr Hopkins began telling people on LinkedIn, Twitter, at his work training sessions and in Access Analytic’s newsletter to vote for his comment.
His suggestion made the top 10 on User Voice and gained the attention of Microsoft.
On August 29, the Excel team at Microsoft said that based on the feedback, they had launched the new XLOOKUP function.
VLOOKUP, which has been a function of Excel since its inception in 1985, allows the user to extract information from a large dataset by typing in a word and finding information located in the corresponding row in the selected column.
The function works in a similar way to how people look at a food item on a menu and then read across the row to find the price in a subsequent column.
According to a Microsoft blog post announcing the change, VLOOKUP is Excel’s third most-used function.
Mr Hopkins said people often had problems with VLOOKUP because it didn’t examine anything to the left of the selected column, meaning users had to rearrange their data to use the function.
Secondly, it required the number of the column the information would be found in, which increased the potential for errors when more columns were added to the spreadsheet.
Users often forgot the fourth part of the formula, which specified whether they wanted exact or approximate matches, giving them incorrect answers.
The new function, XLOOKUP, finds the same information but only requires three inputs – what you are looking for, where to find it, and what to return.
Mr Hopkins told Business News he was really proud to have been involved in the change.
“I’m a massive Excel fan; I’ve been using it for 23 years,” he said.
“I’ve been teaching it for the past 12 years and I’ve seen people struggle and make these mistakes; just knowing that I had a part to play in something that is going to make people’s lives easier is just a really good thing.
“It’s nice to know that the formula will be used for generations to come and I had a bit of a part in it.”
Mr Hopkins said the change would help all who used Excel on a daily basis.
“It is much easier to use and much harder to get wrong,” he said.
“Business reports will be safer, more accurate.
“Accountants, engineers, everybody who uses Excel, it will just benefit everyone.”
Some users may have to wait for the benefits, however.
The new function is being rolled-out to subscribers to the Office 365 Insiders Program, and will be in future versions of the product, but will not be compatible with older versions of Excel including Office 2016 and Office 2019.
