Simpler & Safer Lookup - Vote to change Excel
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
***Thanks to everyone who has voted for this so far, we're now at number 10 on Excel User Voice - please share / retweet etc to encourage others to vote and push it further into the top 10 to make it happen ***
Click here to vote on the Excel User Voice forum
I've written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP. (However, it is a trickier formula and therefore not as widely used).
Most Excel Users just want to return an exact match result from a column (occasionally row) so my suggested ideal formula would be this...
I'm calling it GETMATCH or LOOKUPMATCH
=GETMATCH( A1, MatchColumn/Row, GetColumn/Row, Optional Value if no match)
This is a simpler and more logical formula than the equivalent INDEX MATCH which would currently be written as:
=IFERROR ( INDEX ( GetColumn ), MATCH ( A1, MatchColumn,0)), "TEXT IF ERROR")
Click here to vote on the Excel User Voice forum which allows us users to have our say on what the future of Excel holds. The Excel Team are listening.
Please take 30 seconds to vote for my suggestion of a simpler, safer LOOKUP formula that is a hybrid of VLOOKUP and INDEX MATCH
Thanks
Wyn
AMAZING EXCEL SOLUTIONS
Ik help bedrijven om data om te zetten naar nuttige (stuur)informatie en waardevolle inzichten.
2 年Hi Wyn Hopkins , as far as I know the formula xlookup already does this? I am curious what the getmatch brings extra compared to xlookup?
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
5 年Thanks to everyone that voted we now have SUCCESS!? ?https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
Financial Reporting Analyst at SOCAR Romania I Passed CFA Level 1
7 年PQ is solving this problem (Merge Queries). Besides of this, INDEX(MATCH can work for multiple criteria. What I see from your solution, it gives same result (logically), which gives VLOOKUP. It would be better if there is solution for multiple criteria.
Founder & consultant at Xlconsulting
7 年Every time I teach excel I yearn for this function. VLOOKUP has so many imperfections and IFERROR INDEX MATCH is only accessible to the top 0.01% percentile of the excel users
Director at P3 Adaptive
7 年Wouldn't it be better and simpler if MS reworked the VLOOKUP function to accept negative offsets? So the lookup column (say item number) could be in column C, and you could do VLOOKUP(A1,C3:R100,-2, FALSE) and it would reach to the left of column C two columns to get the data. It would likely be more widely adopted and used vs having to learn a new function. And yes, I know the performance issues with VLOOKUP - they should fix that while inside the code. :-)