Make a simple safer version of VLOOKUP and INDEX MATCH
The majority of Excel users use VLOOKUP to get exact matches from a row or column, It would be good if they all used INDEX MATCH but it's more complex so how about this simpler version for the masses.
=GETMATCH(ReferenceToMatch, MatchColumn/Row, GetColumn/Row, Optional Value if no match)
If seen a few other references to updating VLOOKUPS and there are lots of opportunities, but it would be good to address the simple most widely used issue first.
Wyn Hopkins
shared this idea
And that’s a wrap! XLOOKUP has been released to production. All Office 365 users in the Monthly Channel should have it in their Excel now. Users in the Semi-Annual Channel should have it starting in July.
You can read about XLOOKUP here: https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
Thanks for voting! Make sure you move your votes to another request.
Joe McDaid
128 comments
-
Anonymous
commented
I agree basically. However a new lookup function should replace all other lookup functions and be labeled as such (like a universal lookup function, xlookup as someone suggested, new lookup or whatever). That would be much easier for everyone than trying to remember which lookup functions have limitations for this and that and would eliminate lengthy forums like this if it worked correctly and intuitively. I don't mind Index match but why not call it something intuitive like ideas mentioned earlier?
I'm not suggesting getting rid of old functions because they are still used in old spreadsheets, but help windows and lists could be updated to state those are only for backwards compatibility.
I think one function with multiple criteria as someone else has suggested. The existing function, even if works by having to nest a bunch of things, is too difficult and time consuming to deal with and prone to problems because of the complexity. This is not about just the experts who develop spreadsheets every day. Having multiple criteria and options on one function can admittedly also be complex, but they can be optional and its easier than trying to figure out which of the many lookup functions to use every time you want to use them.
-
AHarris
commented
There has been a lot of interesting and usefull discussion on this topic - thanks everyone.
My question is really simple:
Your proposed function has 4 parameters, VLOOKUP/HLOOKUP has 4 parameters. Would this actually be simpler for beginners?(I last taught beginners about 15 years ago so your experience is probably more recent than mine. I am genuinely interested to hear your take on this.)
Understanding the utility of "Optional Value if no match" does not seem simpler than understinding the "Optional match type" parameter. The utility of using it for rows or columns or mixing row/column like we can with INDEX/MATCH is good but is it simpler?
A Get Column/Row would make the function less prone to breakage than a VLOOKUP/HLOOKUP which will help simplify the life of users. In my experience many users can figure out how to use a VLOOKUP but when it breaks they don't know how to diagnose it.
The "Value if no Match" would preclude the need for nesting with an IFERROR but by the time a user understands the concepts well enough to utilise this optional parameter effectively, they could probably use the nested functions.
Finally, only returning exact matches will suit 95% of scenarios but when the user needs a closest match they will have to go and learn VLOOKUP/HLOOKUP or INDEX/MATCH anyway and Excel will have added yet another way of looking things up from data. The very fact there are so many ways of doing it already, is a source of confusion for beginner/intermediate users. Will the benefit of this function be worth the addition of another function? idk.
-
Akkaz
commented
much needed.
Just more thing to add, it should handle multiple criteria -
Crispo Mwangi
commented
VLOOKUP can do as much as INDEX/MATCH if you nest other functions.
It can even do a right side lookup. See article
http://crispexcel.com/vlookup-is-dead-or-is-it/ -
Wyn Hopkins
commented
Wow, I’ve not been getting alerts on updates to this so apologies for my lack of response. I’ll double down on my efforts of promoting this now. P.S. thanks Mark and Greg
-
Simon
commented
Sorry I can't read 102 comments to see if this is already mentioned but my ideal vlookup would be:
xlookup(what to find [string], where to find [range], column heading name to find in [string],
Column header name to return [string], orientation [vertical or horizontal], find which [first - first match, n - nth match, No multi -error if multiple matches or return only one])[ ] are attributes for the variables required
[string] would be a “string” or a cell reference that returns a string. In this context a string would be anything (strings, integers, numbers) as now.
Column heading name - could be a string “name” or a reference to a excel table column (table1[Name]) as in this context that makes more sense than a reference to the header of that table column (table1[[#Headers],[Name]]). I guess it would also need to have a number offset, as now, to maintain compatibility and to help those that use a formula to derive the number (its March so offset by 3 type of logic)This lets you;
- look in any column on a range (no more 1st column only)
-Return any result from a column either before or after the searched one.
-It is driven by column headings not column offset which means changing the table (adding columns, changing order) doesn’t break it (but changing column headings could)
-orientation – allows it to be vlookup or hlookup – default would be vlookup
-find which – defaults to first match but can return a specific one (the 3rd match) and more importantly can return an error when more than one result exists.Overall this should behave as if I had joined the two tables in (power) Query, once the join is made I can return any column I choose and it errors if I create a many:many or works with a 1:many.
I already have something similar to this in vba and it works perfectly with Excel tables, which are fantastic.
-
SUNILSGAWDE
commented
VLOOKUP ALWAYS LOOKS UP ON RIGHT SIDE. ITSEAR DOES NOT CHECK FOR COLUMNS TO THE LEFT SIDE. WE NEED THIS FEATURE AS WELL. HOW ABOUT USING NEGATIVE COLUMN NO. FOR EXAMPLE,
VLOOKUP(SEARCH CELL REF, AREA TO CHECK, -2, FALSE)
-
Mark
commented
The best tip about INDEX/MATCH actually came from Wyn Hopkins himself and in my opinion its genius.
Set-up an autocorrect to replace "iii" with =INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0).
Then all you need do is type "iii" in a cell and press enter. Now edit the equation that shows the above and use the names to remind you what reference to put where.The webpage where I found this is: http://www.contextures.com/newsletter/excelnews2016/20160503ctx.html
-
Excel Help
commented
Once I learned INDEX/MATCH, I never looked back. As most people point out, it is powerful and gets the job done. However... beginners usually learn VLOOKUP/HLOOKUP first and when I try to convert er...I mean, teach them INDEX/MATCH, the experience is harrowing because the argument order is reversed and the argument terminology is different. I've literally seen a coworker post a sticky on their well with both formulas written out and arrows describing how the arguments relate to each other. That's the kind of end user we're talking about here, the kind that is less technologically inclined than all of us here on uservoice.
If MSFT or the community believes that keeping VLOOKUP is important, at least consider standardizing the order and wording of the function with its more powerful cousin.
-
Anonymous
commented
while you're about it, add a variable for getting the nth instance of a match too!!!
=GETMATCH(ReferenceToMatch, MatchColumn/Row, GetColumn/Row, Optional Value if no match, optional instance of match)
Thanks!
-
Col Delane
commented
Greg: We'll have to agree to disagree, as I will not hesitate to call out flawed reasoning or specious arguments, even on forums such as this.
I've seen far too many so-called expert/gurus/bloggers who write about what they perceive as "problems" with VLOOKUP or "disadvantages" when unfairly comparing it to an INDEX/MATCH combination (e.g. can't look left, easily corruptible when inserting columns, etc.) - when their claims just don't stack up. VLOOKUP can look left when nested with CHOOSE, and can handle inserted columns when nested with any one of three functions.
I don't believe VLOOKUP is difficult to comprehend - after SUM it was one of the first functions I learned to use. If a user can only use single function formulas then they haven't progressed very far.
-
Greg
commented
Hi Col,
Wyn can write nested functions with his eyes closed. But this isn't about him or you or me or any expert/guru/MVP. It's about the END USER. It looks like in his experience, dealing with thousands of end users, they struggle with nested functions, especially if they don't use them often. By making this suggestion he is trying to make the life of the end user simpler, and thereby maybe more effective or less error prone. It's these incremental step changes that continue to make Excel better.
Please do not criticize contributors or their suggestions in this forum. By all means critically assess their proposal and if you disagree then state why and propose a better solution. In short: if you have nothing constructive to say, say nothing.
-
Col Delane
commented
"Most users just need an exact version of INDEXMATCH without having the complexity of a formula embedded inside another formula."
Seriously? How did anyone ever get to be an expert/guru/MVP without nesting functions?? -
Wyn Hopkins
commented
Tom, I agree with you that INDEX MATCH MATCH is really powerful and I am by no means suggesting we get rid of it nor VLOOKUP. Having worked with and trained hundreds and hundreds of Excel users I just want something simple that beginners will understand. Most users just need an exact version of INDEXMATCH without having the complexity of a formula embedded inside another formula.
-
Wyn Hopkins
commented
Here's how GETMATCH would work (exactly the same as INDEX MATCH except the order is changed and there are not 2 formula invoived).
=GETMATCH(A1, B1:B10, C1:C10, "No Match")
or horizontally
=GETMATCH( A1, B1:H1, B2:H2, "Match Not Found")
-
Anonymous
commented
Hi Wyn,
Somehow I am not able to get correct outcome using GETMATCH. Can you please upload a sample of GETMATCH?
-
Tom Pietrzak
commented
The indexmatch combination allows you to reference a row AND column rather than just a row or just a column. This is way more powerful than the one dimensional vlookup or hlookup. Suggest you revisit your proposed GETMATCH function to say something like (Row ref to match, Col ref to match, Row ref lookup, Col ref lookup, Lookup Date Table/Row/Col, Optional value if no match).
This would allow the function to be used one or two dimensionally -
Anil
commented
I guess I'm not getting it, Wyn.
How are the 'MatchColumn/Row' and 'GetColumn/Row' arguments being generated?
I am not understanding how they would be defined
without using the 2 MATCH functions normally used.Are they new nested functions?
If they are just functional arguments, how are you determining the values?Perhaps if you can write out a full sample formula, the light will go on....?
-
Wyn Hopkins
commented
Anil, it’s a simpler version of INDEX MATCH. A single formula that’s easier to understand and write and therefore accessible to more users. Ideally it would have the iferror wrapper built in also to make things even easier.
-
Anil
commented
I do not see a significant difference between your example
and the INDEX function I normally use.My typical formula is
=IFERROR(INDEX(Data!$A:$AA,
MATCH(CurrentDate, Data!$1:$1, false),
MATCH("abc", Data!$A:$A, false)), 0)Other than the terminology, what is the added benefit...?