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.

433 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 →

    87 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...
      • A.C. WILSONA.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 HopkinsWyn 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.

      • Wyn HopkinsWyn Hopkins commented  ·   ·  Flag as inappropriate

        After testing this idea out with around 200 Excel course attendees over the last year 2 things have become clearer. 1. People really like this idea. 2 the name GETMATCH might be better named LOOKUPMATCH in that it then captures elements of familiarity to the VLOOKUP AND INDEX MATCH users.

        The sole reason (in my view) that the majority of users use VLOOKUP is that INDEX MATCH is the "wrong way round". Fix that with this suggestion and we'll make Excel even better

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

      ← Previous 1 3 4 5

      Feedback and Knowledge Base