Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

572 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Wyn Hopkins shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Accepting Votes  ·  AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

    Thanks for the suggestion Wyn! Thanks also to all the contributors to this thread for the thoughtful discussion and debate.

    Of the many lookup function requests, this is currently one of the highest ranking on UserVoice. Please keep the votes rolling in if you’d like to see this, it helps us prioritize new feature work.

    JoeMcD [MS XL]

    102 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • SUNILSGAWDE commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        "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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        Hi Wyn,

        Somehow I am not able to get correct outcome using GETMATCH. Can you please upload a sample of GETMATCH?

      • Tom Pietrzak commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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...?

      • A.C. WILSON commented  ·   ·  Flag as inappropriate

        This has also been suggested in other posts in this forum.
        I favor it, because INDEX(,(MATCH(... is too complex/obtuse for some users, and VLOOKUP(... lacks power & precision.

      • Wyn Hopkins commented  ·   ·  Flag as inappropriate

        Re VLOOKUP and table parameters, that's pretty much how INDEX MATCH works already. That's another benefit highlighted of why INDEXMATCH is more useful than VLOOKUP.

      • Anonymous commented  ·   ·  Flag as inappropriate

        I'm not in favor of GETMATCH. There is no reason to add yet another function when VLOOKUP is so widely used and understood. I'd like to see VLOOKUP extended to use defined tables where the second parameter is the table name and the third parameter is the column name. A third parameter of 3 is cryptic where "Price" is meaningful. Especially when you are viewing someone else's workbook.

      ← Previous 1 3 4 5 6

      Feedback and Knowledge Base