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.

343 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Wyn HopkinsWyn Hopkins shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Anonymous shared a merged idea: Vlookup is horribly complicated -- Need a more point-and-shoot method to copy data from a column from one spreadsheet to another  ·   · 
    Anthony NewellAnthony Newell shared a merged idea: Make a more efficient function that encompasses INDEX and MATCH  ·   · 
    Corey BeckerCorey Becker shared a merged idea: Re-engineer VLOOKUP to add the flexibility of index/match while maintaining its simplicity  ·   · 
    Wyn HopkinsWyn Hopkins shared a merged idea: Replace Index Match with a single formula  ·   · 
    Ray PankoRay Panko shared a merged idea: TVLOOKUP (Table VLOOKUP)  ·   · 

    81 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Wyn HopkinsWyn Hopkins commented  ·   ·  Flag as inappropriate

        No VBA required, existing functionality already exists, just the order of parameters need changing from this

        =IFERROR ( INDEX ( GetColumn ), MATCH (ReferenceToMatch, MatchColumn,0)), "TEXT IF ERROR")

      • Anonymous commented  ·   ·  Flag as inappropriate

        Can you please post an example of your GETMATCH function, with the VBA that will drive it?

      • Wyn HopkinsWyn Hopkins commented  ·   ·  Flag as inappropriate

        Hi Anthony, I see what your saying but that's not in keeping with my hope of making a simpler version of INDEX MATCH. My suggestion is targetted at the 95% of users who need single column (occasionally row) lookups.

      • Antony PihutAntony Pihut commented  ·   ·  Flag as inappropriate

        GETMATCH can be improved like SUMIF was upgraded to SUMIFS.
        Original INDEX/MATCH combination can be used for 2 dimensional search in a table when you indicate a MATCH for a Row and a MATCH for a Column. In that case we would need 2 references to match. What about multidimensional table(cube)?
        I would suggest a closer to current INDEX syntax:
        =INDEXS(TableToSearchIn, R1CriteriaRange, R1Criteria, R2..., C1CriteriaRange, C1Criteria, C2...). R for Rows to match downand C for Columns to match across. Currently it must be an array formula to achieve this.

      • Wyn HopkinsWyn Hopkins commented  ·   ·  Flag as inappropriate

        Mike, I agree about the fact that this will also provide a simpler alternative to HLOOKUP. Unfortunately I am blocked from making any changes to my suggestion now that people are voting for it (Thanks everyone by the way!). I can't even change my spelling mistake :)

      • MikeMike commented  ·   ·  Flag as inappropriate

        Just to be clear, include HLOOKUP in the description of what it will replace.

      • WongCLWongCL commented  ·   ·  Flag as inappropriate

        I'm indifferent on the use to either the said functions. In my opinion, SIMPLICITY is the key to deciding which function to use. Go go GETMATCH !

      • David OlsonDavid Olson commented  ·   ·  Flag as inappropriate

        Personally, I much prefer INDEX MATCH vs. VLOOKUP, but I would love an easier solution.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Agreed. I use Index Match all the time, rather than Lookup, but this would definitely simplify matters.

      • Harlan GroveHarlan Grove commented  ·   ·  Flag as inappropriate

        @Anonymous,

        PowerQuery is great for pulling data from external sources. It's overkill and slower getting data already within the same workbook as the formulas accessing that data.

      • CharlesCharles commented  ·   ·  Flag as inappropriate

        And have an option to search for multiple columns. So first argument (ref to match) could be a 1x2 range, and the second (match column) would be a nx2 range.

      • Corey BeckerCorey Becker commented  ·   ·  Flag as inappropriate

        I think it would be cool if there was an input for which match to return (0 to return an array of the matched values, -1 to return the last occurrence, 1+ to return the corresponding match). Hopefully that would be an optional input and wouldn't hurt performance for most use cases.

      • Charles WilliamsCharles Williams commented  ·   ·  Flag as inappropriate

        I agree that a simple form of exact match lookup is needed that is optimised for both unsorted and sorted data. Performance can be significantly improved for linear search by storing the last index found and rechecking it next time. My suggested implementation of the simple form (MEMLOOKUP and MEMMATCH) handles both sorted and unsorted data, always gives an exact match and allows the column to be either a number (for compatibility with existing functions) or a column label. More complex variations of LOOKUPS are handled by the other members of the AVLOOKUP family (the function gets too complex to use if you try to satisfy all the options with a single function). You can try out these implementations - download from http://www.decisionmodels.com/Downloads/InstallFxlV3.zip

      • Kenneth BarberKenneth Barber commented  ·   ·  Flag as inappropriate

        Harlan,

        For your "re tangent", the =+ notation isn't the fault of Lotus 1-2-3 users. There's nothing wrong with using + to start a formula. The question is: why does Excel keep the +? I think that you could make a suggestion out of that.

      ← Previous 1 3 4 5

      Feedback and Knowledge Base

      How it works

      • Create a user account if you want to add a new suggestion or vote on existing suggestions.
      • Select one of the feedback forums listed.
      • Check out the ideas others have suggested and vote on your favorites.
      • Be sure to search for your suggestion but if you can’t find something similar enough, you can submit your own.
      • Keep suggestions focused on a single idea per post and limited to 25 words or less.
      • When you post an idea to our forum, others will be able to subscribe to it and make comments.