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...
      • Anonymous commented  ·   ·  Flag as inappropriate

        Vlookup alternative for neophytes to match up datat from one spreadsheet to another based on a common data link. End-users scream at us that Vlookup is impossible unless they are programmers. I agree Vlookup is just way too complicated.

      • Paul K Dick commented  ·   ·  Flag as inappropriate

        Of the GETMATCH proposal, I especially like the last paramater (value if not found). I think its a pain to have to code an IFERROR with each lookup I do. I must admit that the IFERROR is a **BIG** improvement to ISERROR.

        Paul

      • Harlan Grove commented  ·   ·  Flag as inappropriate

        Kenneth,

        VLOOKUP (and HLOOKUP) still work well for threshold lookups on sorted tables, though INDEX+MATCH also works for that. Indeed, since MATCH can handle ascending and descending orders, INDEX+MATCH is more flexible for that too.

        OK, find, time to deprecate the lookup functions, but no need for GETMATCH without providing at least the flexibility of INDEX(...,MATCH(...,...,1)), INDEX(...,MATCH(...,...,0)) and INDEX(...,MATCH(...,...,-1)).

        For me, the absolute ideal would be structured references from Excel, regular expressions from LibreOffice Calc, and the FILTER function from Google Sheets. Maybe also the resurrection of SQL.REQUEST and SQL queries against tables.

      • Harlan Grove commented  ·   ·  Flag as inappropriate

        Wyn,

        GETMATCH being the only function aside from IFERROR to provide an alternative return value doesn't make sense. If all the lookup functions did so, much better.

        Excel is becoming as encrusted as PL/I back in the bad old days of the early 1970s, just before C and its descendants took over. At the very least, it's time to deprecate the Lotus 1-2-3-like database functions and the rather pointless *IF/*IFS functions in favor of the truly more useful and not difficult to explain FILTER function.

      • Wyn Hopkins commented  ·   ·  Flag as inappropriate

        While variants of formulas can serve experienced users brilliantly we still need basic safe and simple formulas for the general Excel user population.
        That's who my suggestion is aimed at. Keep it simple with one formula with one purpose.
        Simple to use, simple to review / audit

      • Harlan Grove commented  ·   ·  Flag as inappropriate

        Furthering Kenneth's point, FILTER would eliminate the need for

        COUNTIF
        COUNTIFS
        SUMIF
        SUMIFS
        AVERAGEIF
        AVERAGEIFS
        MAXIFS
        MINIFS
        DAVERAGE
        DCOUNT
        DCOUNTA
        DGET
        DMAX
        DMIN
        DPRODUCT
        DSTDEV
        DSTDEVP
        DSUM
        DVAR
        DVARP
        INDEX+exact MATCH
        HLOOKUP (exact)
        VLOOKUP (exact)

        and very likely over half of actual use of LARGE, SMALL and ROW.

        As for Wyn's GETMATCH, just add a 4th argument to LOOKUP to let it do exact and threshold matching. Make that optional 4th arge 1/0/-1 like MATCH, so the lookup array/range could be sorted in either order.

      • Kenneth Barber commented  ·   ·  Flag as inappropriate

        Wyn, see my post here:
        https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/12934806-google-sheets-filter-function-to-solve-all-ifs-su

        To summarize what Harlan said, FILTER can easily handle multi-criterion lookups and can simulate a "get the nth match" function using INDEX(FILTER(),n). When n = 1, it acts like a multi-criterion INDEX/MATCH.

        For me, that's only half of the beauty of FILTER. The other half is doing away with functions like SUMIFS or MAXIFS. FILTER is like a lemma. It's not very useful on its own, but it is extremely versatile when paired with other functions.
        https://en.wikipedia.org/wiki/Lemma_(mathematics)

        Wyn, I don't entirely agree with your idea of trying to integrate IFERROR into a lookup function, since there are other functions that you could try to integrate. For example, you could integrate INDEX by having an "nth match" parameter, or MATCH by having a parameter to indicate whether or not the range being searched is sorted.

        However, integrating IFERROR into a lookup function is better than the approach that Microsoft seemed to take. IFNA was recently introduced to deal with the "no match" scenario for VLOOKUP and MATCH. It's like IFERROR, but it's specific to the #N/A error.

      • Wyn Hopkins commented  ·   ·  Flag as inappropriate

        Hi Harlan, I like the idea of a multiple criteria lookup function and I think there are a few suggestions for this elsewhere on the UserVoice.

        I think both our approaches are the same just the syntax is re-arranged?

        The simpler the better, the more options that are provided to users the more daunting most users find it and the more likely they end up with an incorrect formula.

        My suggestion of having an optional value if no match again does away with the need for an IFERROR wrapper.

      • Harlan Grove commented  ·   ·  Flag as inappropriate

        Wyn,

        If you have Google Sheet's FILTER,

        =INDEX(X:X,MATCH(foo,G:G,0)) == =GETMATCH(foo,G:G,X:X) == =FILTER(X:X,G:G=foo)

        Note that if GETMATCH returned #N/A when no match was found, it's easy enough to handle alternative return values using IFERROR.

        However, even better,

        =INDEX(X:X,MATCH(TRUE,IF(G:G=foo,J:J=bar),0)) == =FILTER(X:X,G:G=foo,J:J=bar)

        For that matter,

        =INDEX(X:X,SMALL(IF(G:G=foo,ROW(X:X)),n)) == =INDEX(FILTER((X:X,G:G=foo),n)

        An equivalent for Google Sheets's FILTER function would be more flexible than INDEX+MATCH or GETMATCH (which is LOOKUP with exact matching).

      • Wyn Hopkins commented  ·   ·  Flag as inappropriate

        Harlan, if you use index match then any column can be the key field. That's why I prefer that approach to VLOOKUP. This suggested formula would make that even simpler.

      • Wyn Hopkins commented  ·   ·  Flag as inappropriate

        Daniel, one extra thought is you could rename the columns as part of the query to make them more meaningful (either within MSQuery or using PowerQuery)

      • Daniel commented  ·   ·  Flag as inappropriate

        @Wyn
        I agree Tables makes life much easier, but depending on where the information comes from the column headers can be meaningless. I am constantly using MSQUERY to farm information from our AS400 back into an Excel Table but I still define the names of table to make my formulas more meaningful/understandable. Defining the names may take an extra couple minutes up front but in my opinion being able to understand the formula for future modifications makes it well worth the time spent.

      • Kenneth Barber commented  ·   ·  Flag as inappropriate

        Anonymous, your SCHLOOKUP notation can get shorter still if we use MATCH's -1/0/1 instead of VLOOKUP's TRUE/FALSE for the last parameter. What is SCH by the way?

        I disagree with the trade-off that you make (i.e. assuming the first column rather than using an extra parameter). In my work, I have sites as the column being searched. However, these sites fall into more generalized categories, which are also in the table. Naturally, I want the most general information on the left and the most specific information on the right, and so the sites are not the first column.

      • Harlan Grove commented  ·   ·  Flag as inappropriate

        The Table idea is good, but Tables would be better if they also had key fields which didn't need to be first column. That might allow for Table lookups like

        TLOOKUP(keyvalue,TableName,[ResultFieldName])

        Exact matching would always be assumed, but the key field would need to have only distinct values, so could be indexed, so key lookups could be fast.

        Generalized lookups would be nice too, but nicer still would be Google Sheets' FILTER function. With that, the need for LOOKUP, VLOOKUP, HLOOKUP and INDEX+MATCH disappears.

      • Wyn Hopkins commented  ·   ·  Flag as inappropriate

        Hi Daniel, that does make the formula easier to understand after the fact. I'd also fully recommend converting your source data to Tables wherever possible as INDEX MATCH and Tables are brilliant together giving you that meaningful formula and none of the overhead of having to set up named ranges or expand the ranges as more data is added.

      • Daniel commented  ·   ·  Flag as inappropriate

        I agree that some people struggle with the concept of the Index/Match function. I have found that if you first teach them how to define names in the name manager that they have a much easier time grasping the concept. Example is if you have a sheet with 1000 rows and multiple columns and lets say you want to return a number (Total sales to date) from column "C" by matching a Name (sales person) in Cell "D1" by looking for Name in column "A". The formula with undefined names would be =INDEX(C1:C1000,MATCH(D1,A1:A1000,0)) Meaningless for a new user but if you define the names =INDEX(SALESPINDEX,MATCH(SALESPMATCH,SALESTOTLOOK,0)) the formula is much easier to comprehend for the newer user.

      Feedback and Knowledge Base